472,099 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,099 software developers and data experts.

Using Count or Sum in SQL and return 0 when null

I have written a sql query and I need it to return 0 when it doesn't find any matches to my criteria. I have tried adding iif statements, tried sum, and just Count, all of these methods work fine to return the values when it finds matches, but i need it also to return a 0 when there are no matches. Here is what I got.

Expand|Select|Wrap|Line Numbers
  2. SELECT "CAL Recieved" as Tags, [CAL Input Log].[Received Date],sum(iif([cal input log].[received date],1,0)) AS [Count]
  3. FROM [CAL Input Log]
  4. GROUP BY [CAL Input Log].[Received Date]
  5. HAVING ((([CAL Input Log].[Received Date])=(Date()-1)));
  7. SELECT "CAL Initiated" as Tags , [CAL Input Log].[Initiated Date], Sum(iif([cal input log].[received date],1,0)) AS [Count]
  8. FROM [CAL Input Log]
  9. GROUP BY [CAL Input Log].[Initiated Date]
  10. HAVING ((([CAL Input Log].[Initiated Date])=(Date()-1)));
  12. SELECT "CAL resub" as Tags , [CAL Input Log].[RESUB Recived Date], Count([CAL Input Log].[RESUB Recived Date]) AS [Count]
  13. FROM [CAL Input Log]
  14. GROUP BY [CAL Input Log].[RESUB Recived Date]
  15. HAVING ((([CAL Input Log].[RESUB Recived Date])=(Date()-1)));
  17. SELECT "CDZ Recieved" as Tags, [CDZ Input Log].[Received Date], Count([CDZ Input Log].[Received Date]) AS [Count]
  18. FROM [CDZ Input Log]
  19. GROUP BY [CDZ Input Log].[Received Date]
  20. HAVING ((([CDZ Input Log].[Received Date])=(Date()-1)));
  22. SELECT "CDZ Initiated" as Tags , [CDZ Input Log].[Initiated Date], Count([CDZ Input Log].[Initiated Date]) AS [Count]
  23. FROM [CDZ Input Log]
  24. GROUP BY [CDZ Input Log].[Initiated Date]
  25. HAVING ((([CDZ Input Log].[Initiated Date])=(Date()-1)));
  26. UNION ALL SELECT "CDZ resub" as Tags , [CDZ Input Log].[RESUB Recieved Date], Count([CDZ Input Log].[RESUB Recieved Date]) AS [Count]
  27. FROM [CDZ Input Log]
  28. GROUP BY [CDZ Input Log].[RESUB Recieved Date]
  29. HAVING ((([CDZ Input Log].[RESUB Recieved Date])=(Date()-1)));
I also have another issue completly seperate, I need to another clause in there for "Open Tags" but it needs to search (from [CAL Input Log]) when "recieved date" is not null, and "complete date" is null and "resub complete date" is null. then count the matches. Hope this all makes sense.

Thanks for help with either or both issues in advance.

Oct 9 '07 #1
3 9669
3,080 Expert 2GB
I'll start with the first problem. When there's no match nothing will appear, this can only be handled by an additional table holding all unique "CAL Recieved", "CAL ...", .. values and being LEFT (or Right) Joined with the results of this UNION query.
Using such an OuterJoin forcing the "CAL..." values to show always will give a Null value you can transform into zero.

The second problem should be handled with a query just selecting the dates as described. First create a GroupBy query and when having set the GroupBy and Count switch to the SQL mode. After the FROM and before the HAVING clause insert a WHERE clause with the IsNull() and Not IsNull() dates. This will "strip" the rows you don't want to include before grouping and counting.
Finally add this query to the UNION.

Oct 10 '07 #2
Thank You everything worked out.
Oct 10 '07 #3
3,080 Expert 2GB
Well done, learning fast :-)

Success with your application !

Oct 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Random Person | last post: by
1 post views Thread by Daveyk0 | last post: by
4 posts views Thread by onecorp | last post: by
26 posts views Thread by Ping | last post: by
36 posts views Thread by pereges | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.