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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.