CODEWITHSUNDEEP
mysql
mhndlsz
mhndlsz

Reputation: 106

sql query SUM IF GROUP BY

This is my table expense:

id2 name exp date ...
1  Jack 2
2  Joe  3.1
3  Marian 5.8
1  Jack  2
1  Jack  4
3  Marian 3

I want this query: sum up all person expenses and if the expenses was more than 5 echo it.

I have a problem on if section (if more than 5). I grouped by id2 and SUM(exp) and works fine but I don't know how to imply IF. I wrote SUM(IF(exp >= 5, 1, 0)) but sql error says is not correct group function.

S`ELECT id2, SUM(IF(exp >= 5, 1, 0)) as ex FROM expense GROUP BY id2`

I appreciate if you can help me with the query. Thanks

Upvotes: 0

Views: 3474

Answers (2)

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

select id2,name,sum(exp) from expense group by user_id having sum(exp)>5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

It is possible that this is what you want:

SELECT id2, SUM(exp),
       GROUP_CONCAT(CASE WHEN exp > 5 THEN exp END) as ExpsGreaterThan5
FROM expense
GROUP BY id2;

Upvotes: 1

Related Questions