Hello,
I have a table with a few hundred rows. each row contains a date field, for the date a student registered for a course.
I need to build an array which gives me a sum of signups for each month for the last 12 months.
Example, January, 12 signups
feb, 14 signups
The array should look like this after the query is complete.
In this format:
$signupsbymonth=array(
"Jan" => 110,
"Feb" => 130,
"Mar" => 215,
"Apr" => 81,
"May" => 175,
"Jun" => 110,
"Jul" => 190,
"Aug" => 175,
"Sep" => 390,
"Oct" => 286,
"Nov" => 150,
"Dec" => 196
);
can anyone assist? THANKS A MILLION!
r937
January 19, 2012, 1:06am
2
SELECT MONTH(signupdate) AS mm
, DATE_FORMAT(signupdate,'%b') AS mth
, COUNT(*) AS signups
FROM daTable
GROUP
BY mm
ORDER
BY mm
thanks for this. did a query using the code provided and it did work,
However it joined all years together. would like array to say
$values=array(
"January 2012" => 25
"December 2011" => 30
"November 2011" => 22
And to go back 12 months. This query only gave me 2 months worth.
Also, how do I constuct the array? Would like to print it out in the format above.
r937
January 19, 2012, 4:26am
4
doh!! i forgot the WHERE condition!
WHERE signupdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
- INTERVAL 1 YEAR
and if you want to append the year to the month, change the DATE_FORMAT format string from ‘%b’ to ‘%b %Y’
ok perfect that worked. THANKS.now can anyone help with getting it into the array format above?