Count data with mysql query-CodePudding

i have this following table

id   kelurahan    status
1    Pegambiran   Netral
2    Pegambiran   Netral
3    Kejaksan     Positif
4    Kesenden     Positif
5    Pegambiran   Negatif

i want to get result like this

kelurahan    count_positif   count_netral   count_negatif   total
Pegambiran   0               2              1               3
Kejaksan     1               0              0               1
Kesenden     1               0              0               1

i tried this query

SELECT kelurahan, 
(SELECT COUNT(status) FROM tbl_monitoring WHERE status = 'Positif' GROUP BY kelurahan LIMIT 1) AS count_positif, 
(SELECT COUNT(status) FROM tbl_monitoring WHERE status = 'Netral' GROUP BY kelurahan) AS count_netral,
(SELECT COUNT(status) FROM tbl_monitoring WHERE status = 'Negatif' GROUP BY kelurahan) AS count_negatif, 
COUNT(kelurahan) AS total 
FROM tbl_monitoring GROUP BY kelurahan

i get the result like this

any help would be appreciated, thanks.

CodePudding user response：

You should use SUM, not COUNT.
Tested on dbfiddle

SELECT 
  kelurahan, 
  SUM(CASE WHEN status = 'Positif' THEN 1 ELSE 0 END) AS count_positif,
  SUM(CASE WHEN status = 'Netral' THEN 1 ELSE 0 END) AS count_netral,
  SUM(CASE WHEN status = 'Negatif' THEN 1 ELSE 0 END) AS count_negatif,
  SUM(1) AS total
FROM tbl_monitoring
GROUP BY kelurahan;