sql - 按列分组并获取该列的总和

Question

我有一张 table :

------------------------------------
ReportDate  | DAGName | MailboxCount
------------------------------------

这个表中有很多记录。我必须在特定年份的每个月的第一天获取每个 dagName 的邮箱计数总和。

------------------------------------
ReportDate  | DAGName | MailboxCount
------------------------------------
01-01-2012  | abc     |  25
01-02-2012  | xyz     |  55
01-02-2012  | abc     |  125
01-03-2012  | lmn     |  225
01-01-2012  | ghf     |  325
01-03-2012  | kil     |  525
11-03-2012  | kil     |  525
21-03-2012  | kil     |  625
10-05-2012  | jil     |  225
20-11-2012  | kil     |  1525
04-03-2012  | Mil     |  5025

所以我想要的结果是

---------------------------------
Month Name     | Count
---------------------------------
 January       | 350
 Ferbuary      | 150
 March         | 850 

我的查询

SELECT SUM(MailboxCount) AS Count,DagName
              ,MONTH(CAST(ReportDate AS DATETIME))
              ,DATENAME(month, ReportDate)  AS 'Month Name'
        FROM MailboxDatabase
        WHERE (CONVERT(VARCHAR, CONVERT(VARCHAR(10), [ReportDate], 103), 103)
        IN ( '01/01/'+ @year,'01/02/'+ @year,
             '01/03/'+ @year,'01/04/'+ @year,
             '01/05/'+ @year,'01/06/'+ @year,
             '01/07/'+ @year,'01/08/'+ @year,
             '01/09/'+ @year,'01/10/'+ @year,
             '01/11/'+ @year,'01/12/'+ @year 
            ))
        GROUP BY  MONTH(CAST(ReportDate AS DATETIME)),DATENAME(month, ReportDate),DagName  
        ORDER BY 2

如果我的查询附带一些额外的列，我很好。但它没有给我正确的结果。有帮助吗？？

Answer 1

我可能完全误解了这个问题，但以下内容还不够吗？

  SELECT  [Month Name] = DATENAME(month, ReportDate), [Count] = SUM(MailboxCount)
  FROM    MailboxDatabase
  WHERE   DAY(ReportDate) = 1
          AND YEAR(ReportDate) = 2012
  GROUP BY
          DATENAME(month, ReportDate)

测试脚本

;WITH MailboxDatabase AS (
  SELECT * FROM (VALUES
      ('01-01-2012', 25)
    , ('02-01-2012', 55)
    , ('02-01-2012', 125)
    , ('03-01-2012', 225)
    , ('01-01-2012', 325)
    , ('03-01-2012', 525)) AS X(ReportDate, MailboxCount)
)
  SELECT  [Month Name] = DATENAME(month, ReportDate)
          , [Count] = SUM(MailboxCount)
          , Month = MONTH(ReportDate)
  FROM    MailboxDatabase
  WHERE   DAY(ReportDate) = 1
          AND YEAR(ReportDate) = 2012
  GROUP BY
          DATENAME(month, ReportDate), MONTH(ReportDate)
  ORDER BY
          MONTH(ReportDate)