How to get the sum of a column for the current month?

Question

I have a column Amount which I want to get its sum by month within each year.

I have tried the following query and it works well for year but month is summing up incorrectly. For instance in this May, it's summing up all the month of May in various years. So I want it to sum each current month at a time.

DECLARE @currentYear int
DECLARE @currentMonth int

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

SELECT
  SUM(
    CASE
      WHEN 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

Answer 1

SQL SERVER 2012+

This should get you the two sums you need, with a where clause added to never include any amounts outside of the current year:

DECLARE @currentYear INT;
DECLARE @currentMonth INT;

SELECT @currentYear = DATEPART(YEAR, GETDATE());
SELECT @currentMonth = DATEPART(MONTH, GETDATE());

SELECT 
    SUM(CASE WHEN DATEPART(MONTH, [Date]) = @currentMonth THEN Amount ELSE 0 END) AS [This Month],
    SUM(Amount) AS [This Year]
FROM Orders
WHERE 
    [Date] >= DATETIMEFROMPARTS(YEAR(GETDATE()), 1, 1, 0, 0, 0, 0) 
    AND [Date] < DATETIMEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1, 0, 0, 0, 0);

Sql Server 2005/2008

It looks like we don't have DateTimeFromParts yet so here's an alternative:

DECLARE @currentYear INT;
DECLARE @currentMonth INT;
DECLARE @YearStart DATETIME;
DECLARE @YearEndExclusive DATETIME;

SELECT @currentYear = DATEPART(YEAR, GETDATE());
SELECT @currentMonth = DATEPART(MONTH, GETDATE());

SELECT @YearStart = CAST(CAST(@currentYear AS VARCHAR(4)) + '0101' AS DATETIME);
SELECT @YearEndExclusive = CAST(CAST(@currentYear + 1 AS VARCHAR(4)) + '0101'  AS DATETIME);

SELECT 
    SUM(CASE WHEN DATEPART(MONTH, [Date]) = @currentMonth THEN Amount ELSE 0 END) AS [This Month],
    SUM(Amount) AS [This Year]
FROM Orders
WHERE 
    [Date] >= @YearStart 
    AND [Date] < @YearEndExclusive;

Answer 2

Your query returns the total for the current month for all years.

Add a condition on year to the WHERE clause, which then simplifies the year total to just SUM(Amount():

SELECT
  SUM(CASE WHEN DATEPART(month,[Date]) = @currentMonth THEN Amount END) AS 'This Month',
  SUM(Amount) AS 'This Year'
FROM Orders
WHERE DATEPART(year,[Date]) = @currentYear

Also, note that you don't need the ELSE in the CASE, because SUM() ignores nulls (which are returned when no CASE matches).

Answer 3

This won't work for SQL Server 2005, but it will work on SQL Server 2022+. You can use DATETRUNC() to get a date truncated to different time offsets (quarter, month, day, etc.).

SELECT SUM(Amount), DATETRUNC(Date, mm) AS date_truncated
FROM ORDERS
GROUP BY DATETRUNC(Date, mm)

Documentation for DATETRUNC