如何统计当前月份某列的总和？SQL查询修正需求

你的查询中，This Month的统计逻辑只匹配了月份，没有限定年份，所以会累加所有年份里对应月份的Amount数据。要实现仅统计当前年份的当前月份总和，需要在CASE条件里同时判断年份和月份。

修正后的SQL代码：

DECLARE @currentYear int
DECLARE @currentMonth int

SELECT @currentYear = DATEPART(year, GETDATE())
SELECT @currentMonth = DATEPART(month, GETDATE())

SELECT
  SUM(
    CASE
      -- 同时匹配当前年份和当前月份
      WHEN DATEPART(year,[Date]) = @currentYear AND DATEPART(month,[Date]) = @currentMonth THEN Amount
      ELSE 0
    END
  ) AS 'This Month',
  SUM(
    CASE
      WHEN DATEPART(year,[Date]) = @currentYear THEN Amount
      ELSE 0
    END
  ) AS 'This Year'
FROM Orders

另外，也可以用更简洁的日期范围判断来替代DATEPART，若Date列存在索引，这种写法的性能表现会更优：

DECLARE @currentMonthStart DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
DECLARE @nextMonthStart DATE = DATEADD(MONTH, 1, @currentMonthStart)
DECLARE @currentYearStart DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1)

SELECT
  SUM(CASE WHEN [Date] >= @currentMonthStart AND [Date] < @nextMonthStart THEN Amount ELSE 0 END) AS 'This Month',
  SUM(CASE WHEN [Date] >= @currentYearStart AND [Date] < DATEADD(YEAR, 1, @currentYearStart) THEN Amount ELSE 0 END) AS 'This Year'
FROM Orders

内容的提问来源于stack exchange，提问作者User_K47