Using Count or Sum in SQL and return 0 when null

[cmartin1986]

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.

 
SELECT "CAL Recieved" as Tags, [CAL Input Log].[Received Date],sum(iif([cal input log].[received date],1,0)) AS [Count]
FROM [CAL Input Log]
GROUP BY [CAL Input Log].[Received Date]
HAVING ((([CAL Input Log].[Received Date])=(Date()-1)));
UNION ALL
SELECT "CAL Initiated" as Tags , [CAL Input Log].[Initiated Date], Sum(iif([cal input log].[received date],1,0)) AS [Count]
FROM [CAL Input Log]
GROUP BY [CAL Input Log].[Initiated Date]
HAVING ((([CAL Input Log].[Initiated Date])=(Date()-1)));
UNION ALL
SELECT "CAL resub" as Tags , [CAL Input Log].[RESUB Recived Date], Count([CAL Input Log].[RESUB Recived Date]) AS [Count]
FROM [CAL Input Log]
GROUP BY [CAL Input Log].[RESUB Recived Date]
HAVING ((([CAL Input Log].[RESUB Recived Date])=(Date()-1)));
UNION ALL
SELECT "CDZ Recieved" as Tags, [CDZ Input Log].[Received Date], Count([CDZ Input Log].[Received Date]) AS [Count]
FROM [CDZ Input Log]
GROUP BY [CDZ Input Log].[Received Date]
HAVING ((([CDZ Input Log].[Received Date])=(Date()-1)));
UNION ALL
SELECT "CDZ Initiated" as Tags , [CDZ Input Log].[Initiated Date], Count([CDZ Input Log].[Initiated Date]) AS [Count]
FROM [CDZ Input Log]
GROUP BY [CDZ Input Log].[Initiated Date]
HAVING ((([CDZ Input Log].[Initiated Date])=(Date()-1)));
UNION ALL SELECT "CDZ resub" as Tags , [CDZ Input Log].[RESUB Recieved Date], Count([CDZ Input Log].[RESUB Recieved Date]) AS [Count]
FROM [CDZ Input Log]
GROUP BY [CDZ Input Log].[RESUB Recieved Date]
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.

THANKS

[nico5038]

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)

[cmartin1986]

Thank You everything worked out.

[nico5038]

Well done, learning fast :-)

Success with your application !

Nic;o)