6

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.

2
  • 2
    I don't understand your question. Removing the year from the where clause (not the group by clause) won't "clump all the months together". Why do you want that year filter in the where clause?
    – Mat
    Feb 1, 2013 at 17:03
  • 3
    Not sure I understand either. Are you only every interested in a single year and month? If so, how do you expect SQL Server to know which year and month you're interested in, if you don't change it? Feb 1, 2013 at 17:13

4 Answers 4

8

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.

0
4

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.

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
1
  • This is handy but very slow compared to the other answer by Thomas Stringer
    – Gabe
    May 17, 2018 at 15:51
-2

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))
3
  • 2
    Casting to a string, though, will force a table scan. Feb 1, 2013 at 17:13
  • True, Didn't know a better way to do it tough
    – amaters
    Feb 1, 2013 at 17:14
  • 1
    This doesn't compile. Why did you change the GROUP BY?
    – Andriy M
    Feb 2, 2013 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.