Get counts grouped by month and year

Question

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.

Answer 1

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.

Answer 2

You can create a procedure that takes month and year, and optionally just return all data when no parameters are specified, then you can do this (I'm adding a bunch of date handling here instead of string conversion of functions like month/year so that, if crdate is indexed, you'll still have a query that can take advantage of the index):

USE tempdb;
GO
CREATE TABLE dbo.messages
(
  x INT IDENTITY(1,1),
  crdate DATETIME
);

INSERT dbo.messages(crdate) VALUES 
('20100101'),('20100101'),('20100501'),('20110405'),('20120506')

GO

ALTER PROCEDURE dbo.GetYearCounts
  @y INT = NULL,
  @m INT = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sm DATETIME = DATEADD(MONTH, @m-1, DATEADD(YEAR, @y-1900, 0));
  DECLARE @em DATETIME = DATEADD(MONTH, 1, @sm);

  ;WITH x(m,c) AS
  (
    SELECT 
       DATEADD(MONTH, DATEDIFF(MONTH, 0, crdate), 0), 
       COUNT(*)
      FROM dbo.messages
      WHERE crdate >= COALESCE(@sm, '19000101')
      AND crdate < COALESCE(@em, '20300101')
      GROUP BY DATEDIFF(MONTH, 0, crdate)
  )
  SELECT y = YEAR(m), m = MONTH(m), tally = c
    FROM x
    ORDER BY y,m;
END
GO

Now results of two different calls:

EXEC dbo.GetYearCounts 2010, 1;

y      m    tally
----   ---  -----
2010   1    2

EXEC dbo.GetYearCounts;

y      m    tally
----   ---  -----
2010   1    2
2010   5    1
2011   4    1
2012   5    1

If you want to optionally specify a year only and get all 12 months, or specify a month only and get that month across multiple years, the procedure can be modified slightly. It's also tough to tell from your requirements if you want a row even for any months were there were no activity - that can be accomplished as well but we need better requirements.

Answer 3

In SQL Server 2012 and later the format function is very handy for this sort of thing. Note that MM is for months, mm is for minutes. Using yyyy-MM makes sorting very simple.

SELECT count(column1), format(dateColumn, 'yyyy-MM')
FROM table
GROUP BY format(dateColumn, 'yyyy-MM')
ORDER BY 2

Answer 4

Ths should work:

select year(crdate ) as y, month(crdate )  as m, count(*) as tally
from message  
where subject <> 'Welcome' --exclude default messages 
GROUP BY CAST(MONTH(crdate) AS VARCHAR(2)) + '-' + CAST(YEAR(crdate) AS VARCHAR(4))