count a field for each month for 3 year

Posted on 2012-09-14
Last Modified: 2012-09-14
Hi guys,

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
Question by:marian68
    LVL 65

    Assisted Solution

    by:Jim Horn
    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?
    LVL 84

    Accepted Solution

    Have you tried:

    FROM stats
    GROUP BY record_date.MONTH, record_date.Year
    LVL 25

    Assisted Solution

    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.

        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


    Author Closing Comment

    Thank you guys,
    It is possible to count records at intervals of 2 weeks.
    LVL 65

    Expert Comment

    by:Jim Horn
    >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.

    Author Comment

    I think I have to come with another question.
    Thank you

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now