CODEWITHSUNDEEP
mysqlsql
akoDwin
akoDwin

Reputation: 139

MySQL - get COUNT depends on the value

How can I get the COUNT() of the specific field depends on the value of the field? For example I have the field typeOfAssistance , in the query below I got the total numbers of the typeOfAssistance but I have different values in it which is financial medical and burial, How can I add custom column that will divide the total value depends on the value? 

SELECT date,COUNT(*) AS num
FROM requests
WHERE date BETWEEN  DATE_ADD(CURDATE(),INTERVAL -20 DAY) AND CURDATE()
GROUP BY date

desired output:

date       | financial | burial | medical | total

2014-04-25 | 1         | 2      | 3       | 6

Thanks. Sorry for the explanation. :)

Upvotes: 0

Views: 200

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

Typically for something like that I would use SUM rather than COUNT for the item breakdowns.

Something like

SELECT  date,
        SUM(CASE WHEN typeOfAssistance = 'financial' THEN 1 ELSE 0 END) AS financial,
        SUM(CASE WHEN typeOfAssistance = 'burial' THEN 1 ELSE 0 END) AS burial,
        SUM(CASE WHEN typeOfAssistance = 'medical' THEN 1 ELSE 0 END) AS medical, 
        COUNT(1) AS Total
FROM    requests
GROUP BY    date

Upvotes: 4

Related Questions