count a field for each month for 3 year

Hi guys,

SELECT COUNT(id)
FROM stats
GROUP BY record_date.MONTH

It is possible to modify the query so that it count id for each month returning the results for each month of each year, Jan 2009, Jan 2010,  Jan 2011.
Thank you
marian68Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Have you tried:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.MONTH, record_date.Year
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Depends ... is there a column we can use to grab the year?

SELECT month_column, year_column, COUNT(whatever)
FROM your_table
GROUP BY month_column, year_column
ORDER BY month_column, year_column

btw, is record_date a table?  If so, how do you relate it to stats?
0
 
lwadwellConnect With a Mentor Commented:
If it is possible that there are no rows for some months ... and what the query to return 0 for that month - you may need a reference table for each month/year you want reported and join to it.  i.e.

tblRefMonths:
    refMonth    refYear
    ---------------    ------------
    01               2010
    02               2010
    ... etc
    09               2012

Then query like:
SELECT refMonth, refYear, count(*)
FROM tblRefMonths rm
LEFT JOIN your_table yt ON rm.refMonth = MONTH(yt.DateField) AND rm.refYear = YEAR(yt.DateField)
GROUP BY refMonth, refYear

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
marian68Author Commented:
Thank you guys,
It is possible to count records at intervals of 2 weeks.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>It is possible to count records at intervals of 2 weeks.
Sure, but that will be a little tougher, and you have to start by defining what 'intervals of 2 weeks' means.
0
 
marian68Author Commented:
I think I have to come with another question.
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.