CODEWITHSUNDEEP
mysqlgroup-bycase
Anand Rockzz
Anand Rockzz

Reputation: 6658

MySQL Case with group by

I've a table (name=expense) like so:

id  amount  date
1   -1687   2014-01-02 00:00:00.0
2   11000   2014-01-02 00:00:00.0
3   1500    2014-01-03 00:00:00.0
4   -3800   2014-01-03 00:00:00.0
5   119500  2014-01-01 00:00:00.0
6   -2999   2014-01-04 00:00:00.0
7   5972    2014-01-04 00:00:00.0
..
8   7208    2014-12-31 00:00:00.0

I'm able to retrieve income and group it by month like so:

SELECT 
      SUM(amount), 
      date 
 FROM expense 
WHERE YEAR(now()) = YEAR(date) AND amount>0 
GROUP BY MONTH(date);

I'm able to retrieve expense and group it by month like so:

SELECT
      SUM(amount), 
      date 
 FROM expense 
WHERE YEAR(now()) = YEAR(date) AND amount<=0 
GROUP BY MONTH(date);

I want to know if there is a way to combine both these queries and retrieve both income and expense and group it by month in a single query.

Upvotes: 2

Views: 57

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Yes, you need a Case statement:

SELECT
   sum(case when amount> 0 then amount end) as income, 
   sum(case when amount<=0 then amount end) as expense, 
   date 
FROM expense 
WHERE YEAR(now()) = YEAR(date) 
GROUP BY MONTH(date);

Upvotes: 3

Related Questions