sql - Group query results by month and year in postgresql

Question

I have the following database table on a Postgres server:

id      date          Product Sales
1245    01/04/2013    Toys    1000     
1245    01/04/2013    Toys    2000
1231    01/02/2013    Bicycle 50000
456461  01/01/2014    Bananas 4546

I would like to create a query that gives the SUM of the Sales column and groups the results by month and year as follows:

Apr    2013    3000     Toys
Feb    2013    50000    Bicycle
Jan    2014    4546     Bananas

Is there a simple way to do that?

Answer 1

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.