CODEWITHSUNDEEP
sqlms-access
user1871245
user1871245

Reputation:

Return 0 if no records found

I'm trying to count the number of records in a MS Access database per month. so far I have this query and it works fine;

SELECT COUNT(*) AS totalproblems,
problems.department,
departments.dname,
month(start_date) AS month
FROM problems
INNER JOIN departments on problems.department = departments.department_id
WHERE year(start_date) = '2014' 
GROUP BY month(start_date), problems.department, departments.dname

This returns results like so

--------------------------------------------
|totalproblems| department | dname | month |
--------------------------------------------
|10           |1           |bob    |1      |
|3            |2           |sam    |1      |
|8            |3           |mary   |1      |
--------------------------------------------

Which is as expected. What I need though is to also return the departments that didn't have any problems recorded like so

--------------------------------------------
|totalproblems| department | dname | month |
--------------------------------------------
|10           |1           |bob    |1      |
|3            |2           |sam    |1      |
|8            |3           |mary   |1      |
|0            |4           |sue    |1      |
--------------------------------------------

I have tried NZ(COUNT(*),0) AS totalproblems which just created another column with the exact same values as totalproblems minus any 0's

I've tried IIF(ISNULL(totalproblems),0) which returned an empty column

And I've tried SELECT IFNULL(COUNT(*) AS totalproblems,0) Which gives me a Syntax error (missing operator) in query expression

Not sure what else to try or what I'm doing wrong with what I've tried.

Upvotes: 1

Views: 924

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You need an outer join:

SELECT COUNT(problems.department) AS totalproblems, problems.department, departments.dname,
       month(problems.start_date) AS month
FROM departments LEFT JOIN
     problems
     on problems.department = departments.department_id
WHERE (year(problems.start_date) = '2014' OR problems.start_date IS NULL)
GROUP BY month(start_date), problems.department, departments.dname;

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

SELECT COUNT(*) AS totalproblems,
departments.department_id,
departments.dname,
month(start_date) AS month
FROM departments
LEFT JOIN problems on problems.department = departments.department_id
WHERE year(start_date) = '2014' 
GROUP BY month(start_date), department_id.department, departments.dname

Upvotes: 0

Related Questions