SQL Help - Count entries per month but include months with 0 entries

[FragglePete]

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:

 

SELECT MONTH (tblDetentions.DateSetFor) AS MonthGiven, YEAR(tblDetentions.DateSetFor) AS YearGiven, COUNT(tblDetentions.DateSetFor) AS TotalDetentionsCount
FROM tblDetentions
GROUP BY YEAR(tblDetentions.DateSetFor), MONTH(tblDetentions.DateSetFor)
ORDER BY YEAR(tblDetentions.DateSetFor), MONTH(tblDetentions.DateSetFor);

 

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.

 

Would appreciate if any SQL Guru could help.

 

Many thanks in advance

 

Pete

[pauljonze]

How about a simple left join to a table with one columns listing all the months in it?

[ChrisMiles]

In SQL i'd do something like this

 

DECLARE @YearMin INT
DECLARE @YearMax INT
DECLARE @monthMin INT
DECLARE @monthMax INT
DECLARE @CurrentYear INT
DECLARE @CurrentMonth INT

DECLATE @months TABLE(DateYear INT NOT NULL, DateMonth INT NOT NULL)

SELECT @YearMin = ISNULL(MIN(YEAR(tblDetentions.DateSetFor)), YEAR(GETDATE())) FROM tblDetentions
SELECT @YearMax = ISNULL(MAX(YEAR(tblDetentions.DateSetFor)), YEAR(GETDATE())) FROM tblDetentions
SELECT @monthMin = ISNULL(MIN(MONTH(tblDetentions.DateSetFor)), MONTH(GETDATE())) FROM tblDetentions WHERE YEAR(tblDetentions.DateSetFor) = @YearMin
SELECT @monthMax = ISNULL(MAX(MONTH(tblDetentions.DateSetFor)), MONTH(GETDATE())) FROM tblDetentions WHERE YEAR(tblDetentions.DateSetFor) = @YearMax

SELECT @CurrentYear = @YearMin
SELECT @CurrentMonth = @monthMin

WHILE @CurrentYear <= @YearMax
BEGIN
WHILE (@CurrentYear < @YearMax AND @CurrentMonth <= 12) OR (@CurrentYear = @YearMax AND @CurrentMonth <= @monthMax)
BEGIN
	INSERT INTO @months (DateYear, DateMonth) VALUES (@CurrentYear, @CurrentMonth)
	
	SELECT @CurrentMonth = @CurrentMonth + 1
END

SELECT @CurrentYear = @CurrentYear + 1
END

SELECT MONTH(tblMonths.DateMonth) AS MonthGiven, YEAR(tblMonths.DateYear) AS YearGiven, ISNULL(COUNT(tblDetentions.DateSetFor), 0) AS TotalDetentionsCount
FROM @months tblMonths
INNER JOIN tblDetentions
ON MONTH(tblDetentions.DateSetFor) = tblMonths.DateMonth
AND YEAR(tblDetentions.DateSetFor) = tblMonths.DateYear
GROUP BY YEAR(tblMonths.DateYear), MONTH(tblMonths.DateMonth)
ORDER BY YEAR(tblMonths.DateYear), MONTH(tblMonths.DateMonth)

 

Not sure if it will work in access. Couldnt compile it either, but you get the gist

 

Edit: stupid forum is messing up the variable names...

 

Edit 2: Fixed some errors and attached code file

code.txt

Edited July 4, 2011 by ChrisMiles

[FragglePete]

Thanks to both, especially Chris for such a detailed bit of code, however I took Paul's suggestion and did this:

 

SELECT Month(tblDetentions.DateSetFor) AS MonthGiven, Year(tblDetentions.DateSetFor) AS YearGiven, Count(tblDetentions.DateSetFor) AS TotalDetentionsCount, tblMonths.MonthNumber
FROM tblMonths LEFT JOIN tblDetentions
ON tblMonths.MonthNumber=Month(tblDetentions.DateSetFor)
GROUP BY Month(tblDetentions.DateSetFor), Year(tblDetentions.DateSetFor), tblMonths.MonthNumber
ORDER BY tblMonths.MonthNumber

 

Obviously created a new table which has each Month Name and Month Number in first. The above gives the output I can use to create a my nice little bar chart now! Haven't done the 'JOIN' thing for a long time, so just had to get my head round it again.

 

Mucho Thanks to both you!

 

Pete