Group query results by month and year in postgresql

Question

I can't believe the accepted answer has so many upvotes -- it's a horrible method.

Here's the correct way to do it, with date_trunc:

   SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
     FROM yourtable
 GROUP BY txn_month

It's bad practice but you might be forgiven if you use

 GROUP BY 1

in a very simple query.

You can also use

 GROUP BY date_trunc('month', txn_date)

if you don't want to select the date.

select to_char(date,'Mon') as mon,
       extract(year from date) as yyyy,
       sum("Sales") as "Sales"
from yourtable
group by 1,2

At the request of Radu, I will explain that query:

to_char(date,'Mon') as mon, : converts the "date" attribute into the defined format of the short form of month.

extract(year from date) as yyyy : Postgresql's "extract" function is used to extract the YYYY year from the "date" attribute.

sum("Sales") as "Sales" : The SUM() function adds up all the "Sales" values, and supplies a case-sensitive alias, with the case sensitivity maintained by using double-quotes.

group by 1,2 : The GROUP BY function must contain all columns from the SELECT list that are not part of the aggregate (aka, all columns not inside SUM/AVG/MIN/MAX etc functions). This tells the query that the SUM() should be applied for each unique combination of columns, which in this case are the month and year columns. The "1,2" part is a shorthand instead of using the column aliases, though it is probably best to use the full "to_char(...)" and "extract(...)" expressions for readability.

to_char actually lets you pull out the Year and month in one fell swoop!

select to_char(date('2014-05-10'),'Mon-YY') as year_month; --'May-14'
select to_char(date('2014-05-10'),'YYYY-MM') as year_month; --'2014-05'

or in the case of the user's example above:

select to_char(date,'YY-Mon') as year_month
       sum("Sales") as "Sales"
from some_table
group by 1;

There is another way to achieve the result using the date_part() function in postgres.

 SELECT date_part('month', txn_date) AS txn_month, date_part('year', txn_date) AS txn_year, sum(amount) as monthly_sum
     FROM yourtable
 GROUP BY date_part('month', txn_date)

Thanks

Take a look at example 6) of this tutorial -> https://www.postgresqltutorial.com/postgresql-group-by/

You need to call the function on your GROUP BY instead of calling the name of the virtual attribute you created on select. I was doing what all the answers above recommended and I was getting a column 'year_month' does not exist error.

What worked for me was:

SELECT 
    date_trunc('month', created_at), 'MM/YYYY' AS month
FROM 
    "orders"  
GROUP BY 
    date_trunc('month', created_at)

Why not just use date_part function. https://www.postgresql.org/docs/8.0/functions-datetime.html

SELECT date_part('year', txn_date) AS txn_year,
       date_part('month', txn_date) AS txn_month,
       sum(amount) as monthly_sum
FROM payment
GROUP BY txn_year, txn_month
order by txn_year;