Sql-server – Get counts grouped by month and year

sql serversql-server-2008

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year.

SELECT YEAR(crdate) AS y,
       MONTH(crdate) AS m,
       COUNT(*) AS tally
FROM   MESSAGE
WHERE  YEAR(crdate) = 2012
       AND SUBJECT <> 'Welcome' --exclude default messages
GROUP BY
       YEAR(crdate),
       MONTH(crdate)

This is what I have but its limiting in that I need to manually change years. Can this be upgraded to output for all dates in the table in this month/year format?

Removing the year would clump all the months over may years together and distort the data. I am looking to get the counts from that particular month/year.

Best Answer

Wrap this up in a stored procedure then:

CREATE PROCEDURE GetYearCounts
    @year INT
AS
    SELECT YEAR(crdate) AS y,
           MONTH(crdate) AS m,
           COUNT(*) AS tally
    FROM   MESSAGE
    WHERE  YEAR(crdate) = @year
           AND SUBJECT <> 'Welcome' --exclude default messages
    GROUP BY
           YEAR(crdate),
           MONTH(crdate);
GO

This will allow you to specify the year you want to get the data from in the call. I.e.

EXEC GetYearCounts @year = 2012;
GO

This is what I have but its limiting in that I need to manually change years.

Given your requirements there is no way to do this automatically.

Related Question