[FIXED] Count 0 if there is no existing record

Issue

I have a database with 2 tables.

1. Projects

+--------------+--------+
| project_name | Active |
+--------------+--------+
| A            | Yes    |
| B            | No     |
| C            | Yes    |
| D            | Yes    |
+--------------+--------+

2. form-site_safety_inspection

+---------+-----------------+------------------------------+
| project | inspection_date |      Type_of_Inspection      |
+---------+-----------------+------------------------------+
| A       | 2019-08-20      | Supervisor Weekly Inspection |
| D       | 2019-08-20      | Supervisor Weekly Inspection |
| D       | 2019-08-21      | Supervisor Weekly Inspection |
| A       | 2019-08-22      | Supervisor Weekly Inspection |
+---------+-----------------+------------------------------+

project_name from Projects table = project from form-site_safety_inspection table.

New rows are added to form-site_safety_inspection regularly. On a weekly basis I need to count how many rows were added to form-site_safety_inspection by active projects. If there are no rows added by an active project that week, I need to count 0.

So far I coded a query which counts rows in form-site_safety_inspection added by active projects. I'm missing the part of the code which shows a count of 0 if there are no rows submitted by an active project.

SELECT COUNT(*), project

FROM form-site_safety_inspection

LEFT JOIN projects ON form-site_safety_inspection.project = Projects.project_name

WHERE form-site_safety_inspection.Type_of_Inspection= "Supervisor Weekly Inspection"

AND Projects.Active = "Yes"

AND year(inspection_date) = year(curdate())

AND month(inspection_date) = month(curdate())

AND week(inspection_date) = week(curdate())

GROUP BY form-site_safety_inspection.project

ORDER BY form-site_safety_inspection.project

Using the above code I get the result:

+----------+---------+
| COUNT(*) | project |
+----------+---------+
|        2 | A       |
|        2 | D       |
+----------+---------+

I'm looking for the following result:

+----------+---------+
| COUNT(*) | project |
+----------+---------+
|        2 | A       |
|        0 | C       |
|        2 | D       |
+----------+---------+

Solution

Your left join approach is correct, except that the restrictions on the date need to appear in the ON clause of the join, rather than in the WHERE clause, because you don't want them to filter off any projects. Note that the restriction on Active can appear in the WHERE clause, because you don't want to see any inactive projects.

SELECT
    p.project_name,
    COUNT(f.project) AS cnt
FROM Projects p
LEFT JOIN `form-site_safety_inspection` f
    ON p.project_name = f.project AND
    YEARWEEK(inspection_date, 1) = YEARWEEK(CURDATE(), 1)
WHERE
    p.Active = 'Yes'
GROUP BY
    p.project_name;

Demo

Answered By - Tim Biegeleisen