473,388 Members | 981 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 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
  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
3 9997
nico5038
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.

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

Success with your application !

Nic;o)
Oct 10 '07 #4

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

Similar topics

3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
1
by: Daveyk0 | last post by:
Hello there, I have a front end database that I have recently made very many changes to to allow off-line use. I keep copies of the databases on my hard drive and link to them rather than the...
19
by: Paul | last post by:
hi, there, for example, char *mystr="##this is##a examp#le"; I want to replace all the "##" in mystr with "****". How can I do this? I checked all the string functions in C, but did not...
0
by: Lokkju | last post by:
I am pretty much lost here - I am trying to create a managed c++ wrapper for this dll, so that I can use it from c#/vb.net, however, it does not conform to any standard style of coding I have seen....
6
by: ransoma22 | last post by:
I developing an application that receive SMS from a connected GSM handphone, e.g Siemens M55, Nokia 6230,etc through the data cable. The application(VB.NET) will receive the SMS automatically,...
4
by: onecorp | last post by:
I have a SQL table comprised of 31 columns. The first column is simply an id column, the next 30 columns are labelled ,.... The numerical columns have a tinyint type and the data stored is either...
26
by: Ping | last post by:
Hi, I'm wondering if it is useful to extend the count() method of a list to accept a callable object? What it does should be quite intuitive: count the number of items that the callable returns...
36
by: pereges | last post by:
Hi, I am wondering which of the two data structures (link list or array) would be better in my situation. I have to create a list of rays for my ray tracing program. the data structure of ray...
5
by: satyabhaskar | last post by:
hi all, In my web page i have created radio buttons dynamically on to the page .....following is my code string Course, Semester, Section; int rowsCount; string con =...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

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.