Link to home
Start Free TrialLog in
Avatar of JonoBB
JonoBB

asked on

Group by month

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.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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Please check your SQL statement, because the SQL statement in your question appears to be correct for returning the single row per month...
ASKER CERTIFIED SOLUTION
Avatar of JonoBB
JonoBB

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial