SQL Help - Count entries per month but include months with 0 entries
I'm not a great SQL whizz, and I've put together a sort of Student Management Database that has a web front end where teachers record things like detentions, incidents, etc. It's a sort of in house behaviour management thing.
I've been clever with the Google Charts API where I throw the results of a Query to a string which gets thrown to the API and produces nice little bar graphs, etc from various queries showing for example the number of detentions given by house, or by year, etc, etc. Basic stuff really, but it works well and SMT like it.
I'm now trying to produce a query which shows the number of detentions set by Month, which works using this SQL statement in Access:
However, I want it to show all months regardless wether or not a detention has been set. This will make throwing the resulting string out to the Google Charts API easier to show a nice bar chart.
SELECT MONTH (tblDetentions.DateSetFor) AS MonthGiven, YEAR(tblDetentions.DateSetFor) AS YearGiven, COUNT(tblDetentions.DateSetFor) AS TotalDetentionsCount
GROUP BY YEAR(tblDetentions.DateSetFor), MONTH(tblDetentions.DateSetFor)
ORDER BY YEAR(tblDetentions.DateSetFor), MONTH(tblDetentions.DateSetFor);
Would appreciate if any SQL Guru could help.
Many thanks in advance