Group query results by month and year in PostgreSQL

sql

database

date-formatting

group-by

byAnton Shumikhin·Oct 14, 2024

Master the time-travel using PostgreSQL's DATE_TRUNC. This SQL charm groups and summarises your data in a jiffy:

SELECT
  DATE_TRUNC('month', your_date_field) AS month_year,
  SUM(your_sales_field) AS total_sales
FROM
  your_table
GROUP BY
  month_year
ORDER BY
  month_year;

Just swap your_date_field, your_sales_field, and your_table with your real-life ammunition. The resultant output showcases sales summarized by month-year, arranged in the order time moves (we’re still waiting for the time machine to change that!).

Cracking the code: Explained

So, you're back for more? Buckle up. Here, we dissect what just zinged past you and point you toward the hidden Easter Eggs.

Using `to_char` to format dates

Charming your dates into something your boss might find comprehensible.

SELECT
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY') AS month_year, --Jan-2020, Feb-2020, etc. Much better!
  product,
  SUM(sales) AS total_sales
FROM
  sales_table
GROUP BY
  month_year, product
ORDER BY
  month_year, product;

Recall: SQL returns dates in a format that’s hard on the eyes (like dark theme IDEs in bright sunlight). Our to_char charm alleviates the situation.

More than meets the “case”

Enjoyed your anonymous SQL tables? Postgres brings case-sensitivity to the fun. Keep SQL's pedantry in mind and slap some double quotes onto those column names (they like to play hard to get):

SELECT
  to_char(DATE_TRUNC('month', "date_col"), 'Mon-YYYY') AS month_year, -- "Who's a charming date now?"
  "product",
  SUM("sales") AS total_sales
FROM
  "sales_table" -- With the lifeline of these quotes, won’t be long before your liberty is in double quotes too!
GROUP BY
  month_year, "product"
ORDER BY
  month_year;

Group like you mean it

Here's a fun date: A night out involves music, dancing, and GROUP BY:

GROUP BY
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY'), product

Great dates and group dances ensure no one is left behind.

Extracting value: Year and month

Need to extract some separate years and months? Here’s your mantra:

SELECT
  EXTRACT(YEAR FROM date_col) AS year, -- Free me from this biscuit!
  EXTRACT(MONTH FROM date_col) AS month, -- Never did like being a part of the whole…
  SUM(sales) AS total_sales
FROM
  sales_table
GROUP BY
  year, month
ORDER BY
  year, month;

POOF! Now you’ve got separate year and month fields.

Addressing the oddballs

What’s this? Nulls or outliers? Send your worries packing with COALESCE and CASE:

SELECT
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY') AS month_year,
  COALESCE(product, 'Not-A-Clue-Product') AS product, -- Who me? Nope, I don’t remember.
  SUM(COALESCE(sales, 0)) AS miracles_do_happen -- What sales? Let’s pretend there were some!
FROM
  sales_table
GROUP BY
  month_year, product
ORDER BY
  month_year, product;