Need your help/advice/opinion for below sql.
I try to select all count record that exist in tables between to date insert by user.Currently, just put dummy date to test. The result is ok, but it only view for match data. How to include the record that not match as long is between those two date.
Suppose the record will show all record from month January to December but is only show starting with April which it only record that exist in db.I'm thinking to use JOIN to fix this, but not work.Thanks in advance.
SELECT DISTINCT DATE_FORMAT(a.tarikh_terima,'%M')AS bln, COUNT(CASE WHEN a.ID_KATEGORI=1 THEN 1 ELSE NULL END) AS aduan, COUNT(CASE WHEN a.ID_KATEGORI=2 THEN 1 ELSE NULL END) AS komen, COUNT(CASE WHEN a.ID_KATEGORI=3 THEN 1 ELSE NULL END) AS pertanyaan, COUNT(CASE WHEN a.ID_KATEGORI=4 THEN 1 ELSE NULL END) AS cadangan, COUNT(a.id_aduan) AS jumlah FROM s01_aduan a, lkp_bahagian b, s01_aduan_perkara c WHERE a.id_aduan=c.id_aduan AND c.id_bahagian=b.bahagian_id AND a.tarikh_terima BETWEEN STR_TO_DATE('01-01-2010', '%d-%m-%Y') AND STR_TO_DATE('31-12-2010', '%d-%m-%Y') AND b.nama_bahagian='Bahagian Konsular' GROUP BY DATE_FORMAT(a.tarikh_terima,'%M')ASC ORDER BY DATE_FORMAT(a.tarikh_terima,'%M') ASC;