displaying zero in query if data has no value

Question

i want to display the clinic name and my total earnings in my database with a condition of date within year and month.

Here is what i've got:

SELECT tbl_clinics.clinic_name, COALESCE(SUM(tbl_bill.bill_amt),0) AS clinic_tot FROM tbl_clinics
LEFT OUTER JOIN tbl_check_up ON tbl_clinics.clinic_id = tbl_check_up.clinic_id 
LEFT OUTER JOIN tbl_bill ON tbl_check_up.bill_id = tbl_bill.bill_id 
WHERE MONTH(tbl_bill.date_billed) = "01" 
    AND YEAR(tbl_bill.date_billed) = "2018" 
    AND tbl_clinics.user_id = "2-2017" 
GROUP BY tbl_clinics.clinic_name

now my query returns only correct if i have data that was fetch from my database. Now i want something like this: for example I change the date YEAR(tbl_bill.date_billed) = "2019" then i want to output like this:

_________________________________
|   Clinic_name |  clinic_tot   |
---------------------------------
|  Clinic 1     |     0         |
---------------------------------

score 2 · Accepted Answer · answered Jan 18 '18 at 03:06

Your WHERE clause is turning the outer join into an inner join. You need to move the conditions on all but the first table into the ON clause:

SELECT c.clinic_name, COALESCE(SUM(b.bill_amt), 0) AS clinic_tot 
FROM tbl_clinics c LEFT OUTER JOIN
     tbl_check_up cu
     ON c.clinic_id = cu.clinic_id LEFT OUTER JOIN
     tbl_bill b
     ON cu.bill_id = b.bill_id AND
        MONTH(b.date_billed) = 1 AND
        YEAR(b.date_billed) = 2018 
WHERE c.user_id = '2-2017'
GROUP BY c.clinic_name

displaying zero in query if data has no value

1 Answers1