Solved

Show 0 when Count is Zero

Posted on 2011-03-14
6
562 Views
Last Modified: 2012-06-21
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;

Open in new window

0
Comment
Question by:ca1358
6 Comments
 
LVL 9

Expert Comment

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

Ss
0
 

Author Comment

by:ca1358
ID: 35130690
I need syntax of Count(Field) but iif isNull then put a zero.
0
 
LVL 75
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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

by:ca1358
ID: 35141895
Thank you
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35142250
OK
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now