+ Post New Thread
Results 1 to 4 of 4
Coding Thread, SQL Help - Count entries per month but include months with 0 entries in Coding and Web Development; I'm not a great SQL whizz, and I've put together a sort of Student Management Database that has a web ...
  1. #1

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    905
    Thank Post
    287
    Thanked 141 Times in 114 Posts
    Blog Entries
    28
    Rep Power
    42

    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:

    Code:
    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

  2. #2
    pauljonze's Avatar
    Join Date
    May 2008
    Location
    Staffs
    Posts
    86
    Thank Post
    17
    Thanked 22 Times in 15 Posts
    Rep Power
    23
    How about a simple left join to a table with one columns listing all the months in it?

  3. Thanks to pauljonze from:

    FragglePete (5th July 2011)

  4. #3

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    22
    In SQL i'd do something like this

    Code:
    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
    Attached Files Attached Files
    Last edited by ChrisMiles; 4th July 2011 at 07:43 PM.

  5. Thanks to ChrisMiles from:

    FragglePete (5th July 2011)

  6. #4

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    905
    Thank Post
    287
    Thanked 141 Times in 114 Posts
    Blog Entries
    28
    Rep Power
    42
    Thanks to both, especially Chris for such a detailed bit of code, however I took Paul's suggestion and did this:

    Code:
    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



SHARE:
+ Post New Thread

Similar Threads

  1. Strange DHCP entries
    By andypitts in forum Wireless Networks
    Replies: 1
    Last Post: 20th May 2011, 12:00 PM
  2. [SIMS] Duplicate Entries in Lookups
    By Stuart_C in forum MIS Systems
    Replies: 2
    Last Post: 3rd May 2011, 12:01 PM
  3. iMacs and DNS Entries
    By techie08 in forum Mac
    Replies: 7
    Last Post: 11th August 2008, 02:36 PM
  4. Random DNS Entries
    By Jackd in forum Wireless Networks
    Replies: 1
    Last Post: 23rd February 2008, 03:18 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •