jeudi 5 avril 2018

group by is messing up my counts mysql

I have a basic table:

id  client  trans_date  returned
1    bob    20180301       0
2    frank  20180301       0
3    bob    20180401       1

id like to get a result that groups by the client and counts how many items bought and how many returned. Like this

name  bought returned
bob    1       1
frank  1       0

i tried this but it didnt work

SELECT 
     soldto AS name,
     IF(return=0,count(id),'0') AS bought,
     IF(return=1,count(id),'0') AS returned 
FROM sales 
WHERE sdate BETWEEN '20180201000000' AND '20180501000000'
group by soldto;

Aucun commentaire:

Enregistrer un commentaire