Query Months help

Question

Hey all i am in need of some helpful tips/advice on how to go about my problem. I have a database that houses a "signup" table. The date for this table is formated as such:

2010-04-03 00:00:00

Now suppose i have 10 records in this database:

2010-04-03 00:00:00
2010-01-01 00:00:00
2010-06-22 00:00:00
2010-02-08 00:00:00
2010-02-05 00:00:00
2010-03-08 00:00:00
2010-09-29 00:00:00
2010-11-16 00:00:00
2010-04-09 00:00:00
2010-05-21 00:00:00

And i wanted to get each months total registers... so following the example above:

Jan = 1
Feb = 2
Mar = 1
Apr = 2
May = 1
Jun = 1
Jul = 0
Aug = 0
Sep = 1
Oct = 0
Nov = 1
Dec = 0

Now how can i use a query to do that but not have to use a query like:

 WHERE left(date, 7) = '2010-01'

and keep doing that 12 times? I would like it to be a single query call and just have it place the months visits into a array like so:

 do until EOF
   theMonthArray[0] = "total for jan"
   theMonthArray[1] = "total for feb"
   theMonthArray[2] = "total for mar"
   theMonthArray[3] = "total for apr"
   ...etc
 loop

I just can not think of a way to do that other than the example i posted with the 12 query called-one for each month.

This is my query as of right now. Again, this only populates for one month where i am trying to populate all 12 months all at once.

 SELECT count(idNumber) as numVisits, theAccount, signUpDate, theActive
 from userinfo
 WHERE theActive = 'YES'
 AND idNumber = '0203'
 AND theAccount = 'SUB'
 AND left(signUpDate, 7) = '2010-04'
 GROUP BY idNumber
 ORDER BY numVisits;

The example query above outputs this:

 numVisits | theAccount | signUpDate          | theActive
 2           SUB          2010-04-16 00:00:00   YES

Which is correct because i have 2 records within the month of April.

But again, i am trying to do all 12 months at one time (in a single query) so i do not tax the database server as much when compared to doing 12 different query's...

UPDATE I'm looking to do something like along these lines:

 if NOT rst.EOF
    if left(rst("signUpDate"), 7) = "2010-01" then
        theMonthArray[0] = rst("numVisits")
    end if        

    if left(rst("signUpDate"), 7) = "2010-02" then
        theMonthArray[1] = rst("numVisits")
    end if
    etc etc....
 end if

Any help would be great! :)

David

Answer 1

You need to create a derived table with all the months and join that onto your query so that you get zero counts instead of missing rows for those months that have no entries. The following query is a bit long, but it should meet your requirements:

SELECT
    T1.`month`,
    COALESCE(numVisits, 0) AS numVisits
FROM (
    SELECT 1 AS month
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
    UNION ALL
    SELECT 7
    UNION ALL
    SELECT 8
    UNION ALL
    SELECT 9
    UNION ALL
    SELECT 10
    UNION ALL
    SELECT 11
    UNION ALL
    SELECT 12
) AS T1
LEFT JOIN (
    SELECT
        MONTH(signUpDate) AS `month`,
        COUNT(*) AS numVisits
    FROM userinfo
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    AND YEAR(signUpDate) = 2010
    GROUP BY MONTH(signUpDate)
) AS T2 ON T1.`month` = T2.`month`

Answer 2

Ok, got it.

do until oRecordset.EOF
    theMonth(x) = oRecordset("numVisits")
    oRecordset.movenext
    x = x + 1
loop