Group by month

[Jonob]

Hi all,

 

I have a query that is grouping data by month and year:

SELECT DATE_FORMAT(t.date_trans, '%b %Y') as date_trans
, CASE WHEN t.type_id = 1 THEN SUM(t.amount) ELSE 0 END as 'income'
, CASE WHEN t.type_id = 2 THEN SUM(t.amount) ELSE 0 END as 'expenses'
FROM trans t
GROUP BY  DATE_FORMAT(t.utc_date_trans, '%b %Y')
ORDER BY t.date_trans

 

This works fine, but if I have type_id = 1 and type_id = 2 in the same month, then the query returns:

 

date_trans | income | expenses

Jan 2010    | 10        | 0

Jan 2010    | 0          | 5

 

Instead, what I need is:

 

date_trans | income | expenses

Jan 2010    | 10        | 5

 

 

Any help appreciated.

[Jonob]

My bad, seems like it needs to be:

 

...
, SUM(CASE WHEN t.type_id = 1 THEN t.amount ELSE 0 END) as 'income'
, SUM(CASE WHEN t.type_id = 2 THEN t.amount ELSE 0 END) as 'expenses'
...