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
LVL 8
JonoBBAsked:
Who is Participating?
 
JonoBBConnect With a Mentor Author Commented:
Ugh, seems like I have to swap where the SUM is placed.

This works:

SELECT DATE_FORMAT(t.date_trans, '%b %Y') as date_trans
, 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'
FROM trans t
GROUP BY  DATE_FORMAT(t.date_trans, '%b %Y')
ORDER BY t.date_trans
0
 
Patrick MatthewsCommented:
Please check your SQL statement, because the SQL statement in your question appears to be correct for returning the single row per month...
0
All Courses

From novice to tech pro — start learning today.