Link to home
Start Free TrialLog in
Avatar of mhsheldesk
mhsheldesk

asked on

Select total count for each day of the month

Hi,
I'd like to query our database for the total number of events per day. I have attached what I have written so far. I'm most interested for when the total is 0, and this query doesn't present 0 in its rowset.

How can I get this to present 0 and the date when the total is 0 for a particular day of the month

Thanks
SELECT  DATEPART(MONTH, created) AS 'Month',
        DATEPART(day, created) AS N'Day', COUNT(*)                                   
FROM    dbo.table
GROUP BY DATEPART(MONTH, Created), DATEPART(day, Created) 
ORDER BY DATEPART(MONTH, Created), DATEPART(day, Created)

Open in new window

SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have to create some calendar table for given period and (outer) join it with your results.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You probably noted the bug in join condition:
ON tt.iYear = c.Year AND tt.iMonth ...

is correctly
ON tt.iYear = c.iYear AND tt.iMonth ...
Avatar of mhsheldesk
mhsheldesk

ASKER

Thank you very much for your solutions, I spilt this since lwadwell answered first and pcelba gave a complete solution. Thank you again.