I have a table like below and I want to count rows group by each date and status (true or false only), display 0 if no record
date name status
2019-10-01 A true
2019-10-01 B false
2019-10-01 C true
2019-10-02 A true
2019-10-02 B true
How can i display like below? Thanks.
date count status
2019-10-01 2 true
2019-10-01 1 false
2019-10-02 2 true
2019-10-02 0 false
You need to CROSS JOIN the distinct values of date and status to each other to get a table of all combinations of each, and then LEFT JOIN that to the table to get the COUNT values:
SELECT d.date, s.status, COUNT(y.name) AS count
FROM (SELECT DISTINCT date
FROM yourtable) d
CROSS JOIN (SELECT DISTINCT status
FROM yourtable) s
LEFT JOIN yourtable y ON y.date = d.date AND y.status = s.status
GROUP BY d.date, s.status
Output:
date status count
2019-10-01 false 1
2019-10-01 true 2
2019-10-02 false 0
2019-10-02 true 2
If you might have values of status that you want to ignore (perhaps null), change that derived table to a UNION of the values you want to check e.g.
SELECT 'true' AS status
UNION ALL
SELECT 'false'
Use this:
Select count(*) as count from table group by
date,status ;
