Hello guys i have a problem with a SQL query it counts the records per month. From october 2010 to august 2011 the record count is good but for some reason the first month sept 2010 it shows 99 records but that is not correct it should be only 15 records i don't know whats wrong with the query here it is:

PHP Code:

SELECT COUNT(`case_#`) AS CASES, MONTH(`fecha_actual`) AS `Month`, YEAR(`fecha_actual`) AS `Year` FROM `vital_info`
GROUP BY MONTH(`fecha_actual`)
ORDER BY (`fecha_actual`) 


Please any help I would really appreciate it. Thanks

ok I just found the problem. The problem is that it is adding september 2010 and september 2011 now my question is, is there a way to select it by month and year so this would work? please any help would be appreciate it. Thanks

Not sure if the DATE_SUB() function will help at all?
http://www.w3schools.com/sql/func_date_sub.asp

Try grouping by both the year and month, as in

"group by YEAR(`fecha_actual`), MONTH(`fecha_actual`)"

And since you've already created aliases for the year and month, you can use those instead, as in

"group by Year, Month"

Thanks lizciz this actually worked great! now i have a question i need to display the last 12 months i put ORDER BY ('fecha_actual') DESC LIMIT 12 and it works fine but when it displays it i need to display in Ascending order is there a way to do this??

change DESC (descending) to ASC (ascending)

Originally Posted by chrishirst

change DESC (descending) to ASC (ascending)

this wouldn't work because it will show the first 12 on the DB but i need the last 12 order by DESC date from the DB and then display them in ASC order...

set your "print" loop to work backwards.