By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,601 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,601 IT Pros & Developers. It's quick & easy.

Using Count or Sum in SQL and return 0 when null

P: 37
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
  1.  
  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)));
  6. UNION ALL
  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)));
  11. UNION ALL
  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)));
  16. UNION ALL
  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)));
  21. UNION ALL
  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)));
  30.  
  31.  
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.

THANKS
Oct 9 '07 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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.

Nic;o)
Oct 10 '07 #2

P: 37
Thank You everything worked out.
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Well done, learning fast :-)

Success with your application !

Nic;o)
Oct 10 '07 #4

Post your reply

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