x
Solved

Show 0 when Count is Zero

Posted on 2011-03-14
Medium Priority
572 Views
I am getting a count of Records.

What I need to add is, if the count is zero or null  then show the zero has count.

``````SELECT Count(M_AS400_P112.PCode) AS CountOfPCode, M_AS400_P112.MaintenanceDate, M_AS400_P112.PCode INTO TodayCountAS400P112
FROM M_AS400_P112
GROUP BY M_AS400_P112.MaintenanceDate, M_AS400_P112.PCode;
``````
0
Question by:ca1358

LVL 9

Expert Comment

ID: 35130600
MSSQL has ISNULL ... check for something similar for the AS400.

Ss
0

Author Comment

ID: 35130690
I need syntax of Count(Field) but iif isNull then put a zero.
0

LVL 75

Expert Comment

ID: 35130742
Maybe this:

SELECT Count( Nz(M_AS400_P112.PCode , 0 ) ) AS CountOfPCode, M_AS400_P112.MaintenanceDate, M_AS400_P112.PCode INTO TodayCountAS400P112
FROM M_AS400_P112
GROUP BY M_AS400_P112.MaintenanceDate, M_AS400_P112.PCode;

mx
0

LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 35130912
With no criteria specified, why would the count be zero?
Just curious...?
In other words, what is this query seeking to display?

If there are many PCode, then you should include this table in the query.
Then set a left Join to show all records from the PCode table.

Something like this to get a count of all orders for each employee, while displaying Zero for any employee with no orders:
SELECT Count(Orders.OrderID) AS CountOfOrderID, Employees.EmployeeID
FROM Employees LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.EmployeeID;

Sample DB with query attached

Database48.mdb
0

Author Closing Comment

ID: 35141895
Thank you
0

LVL 74

Expert Comment

ID: 35142250
OK
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.