count, group by month and leave zeros

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

count, group by month and leave zeros

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-11-30 : 19:09:36

I couldn't find this anyplace and am surprised.
In my count query I want it to return a zero if there were no events in a room during the month. This may be tricky huh?
At least the way I've been approaching my query.

Any better ideas?

this is the query am using.
gives me similar data examples show in my other posts today.

SELECT Year([DATE]) AS [YEAR], Format(DateSerial(2000,Month([DATE]),1),"mmm") AS [MONTH], Count([encounter]) AS Events, Location
FROM Events_Table
WHERE [Location] NOT IN ('Bar','Toilet','Pool')
GROUP BY Year([DATE]), Month([DATE]), [Location]
ORDER BY Month([DATE]), [Location];

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-30 : 20:30:47

you need a table of all months to consider. create it and populate it will plenty of data, say from the start of what to have until, say, Dec 2020.

since you want data by location also, you then have to create query of all possible months with all possible locations:

select M.Year, M.Month, L.Location
from Months M, Locations L

note there is no join in the above. save it as a query, call it LocationsMonths or something like that.

then do a LEFT OUTER JOIN from LocationsMonths to your existing query.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources