sort and count the value in db

Question

this is my db(salelist)

id type firstname mob affid salestatus

1 refund nike **** 5443 cancelled

2 sale bob **** 5443 ok

3 sale mike **** 5123 ok

4 sale john **** 5443 ok

5 refund cat **** 5443 cancelled

6 sale mony **** 5443 ok

7 refund ity **** 5123 cancelled

i want to sort and count how many sale and refund get to each affiliate
i want result like this in a table

affid        sale           refund
5443       3               2
5123       1               1

please help to find out

this is my code mycode

i got this error while running mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\SANN\check\affiliateslist.php on line 23

score 1 · Answer 1 · answered Mar 03 '16 at 17:36

1

You could use conditional case expressions to count the relevant sale types:

SELECT   affid,
         COUNT (CASE type WHEN 'sale' THEN 1 END) AS sale,
         COUNT (CASE type WHEN 'refund' THEN 1 END) AS refund
FROM     salelist
GROUP BY affid

answered Mar 03 '16 at 17:36

Mureinik

297,002
52
306
350

i got this error mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\SANN\check\affiliateslist.php on line 23 – Sananth Mar 03 '16 at 18:16

score 1 · Answer 2 · answered Mar 03 '16 at 17:38

1

SELECT affid, 
SUM(case when `type` = 'sale' then 1 else 0 end) sale,
SUM(case when `type` = 'refund' then 1 else 0 end) refund
FROM salelist
GROUP BY affid

answered Mar 03 '16 at 17:38

Rodrigo C

151
6

sort and count the value in db

this is my db(salelist)

2 Answers2