Adding all the amounts per month using sql server

Question

Here is my data

date           amount
2017-07-10     15.00
2017-07-10     15.00
2017-07-28     25.00
2017-08-01     100.00
2017-08-12     15.00
2017-08-29     200.00
2017-09-18     105.00
2017-09-21     200.00
2017-09-23     25.00
2017-10-12     15.00
2017-10-14     500.00
2017-11-01     200.00
2017-11-02     200.00

I want to add it by month so what we will get that in June i got a total of 55, August i will get 315, September 330, October 515, November 400 and the past dates with no amount will be 0 how will i do that?

Here is my temporary table codes:

create table #TempTable
    (month varchar(50),
    amount decimal(18,2))


insert into #TempTable (month)
SELECT TOP 12
DATENAME(MONTH, DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY object_id) - 1,0))
FROM sys.columns

create table #Data
    (date date,
    amount decimal(18,2))
  insert into #Data(date,amount) values('2017-07-10',15.00)
  insert into #Data(date,amount) values('2017-07-10',15.00)
  insert into #Data(date,amount) values('2017-07-28',25.00)
  insert into #Data(date,amount) values('2017-08-01',100.00)
  insert into #Data(date,amount) values('2017-08-12',15.00)
  insert into #Data(date,amount) values('2017-08-29',200.00)
  insert into #Data(date,amount) values('2017-09-18',105.00)
  insert into #Data(date,amount) values('2017-09-21',200.00)
  insert into #Data(date,amount) values('2017-09-23',25.00)
  insert into #Data(date,amount) values('2017-10-12',15.00)
  insert into #Data(date,amount) values('2017-10-14',500.00)
  insert into #Data(date,amount) values('2017-11-01',200.00)
  insert into #Data(date,amount) values('2017-11-02',200.00)

  select * from #Data
  select * from #TempTable
drop table #TempTable
drop table #Data

PS. Just update the #TempTable and put the total on it thank you :)

Plus for one test data,also please include expected result as text and any query attempt — TheGameiswar, Nov 02 '17 at 14:02
Possible duplicate of [SQL Server Group By Month](https://stackoverflow.com/questions/8345282/sql-server-group-by-month) — DavidG, Nov 02 '17 at 14:03
Remember if you group by month, you probably need to group by year if you date range is longer than 12 months. — DavidG, Nov 02 '17 at 14:03

Stanislav Kundii · Answer 1 · 2017-11-02T14:09:40.133

1

  SELECT a.Month,SUM(ISNULL(b.Amount,0)) 
  FROM  #TempTable a
  LEFT JOIN #Data b
  ON a.Month = DATENAME(MONTH,date)
  GROUP BY a.Month

for update

   UPDATE a
  SET Amount = ISNULL(GroupSuma,0)
  FROM  #TempTable a
  LEFT JOIN
  (
      select DATENAME(MONTH,date) as month, SUM(b.Amount) as GroupSuma
      FROM #Data b
      GROUP BY DATENAME(MONTH,date)
  ) as c
  ON a.Month = c.Month

edited Nov 02 '17 at 14:09

answered Nov 02 '17 at 14:03

Stanislav Kundii

2,874
1
11
17

This will fail if the date range is longer than 1 year. – DavidG Nov 02 '17 at 14:05
You got it man, but the thing is the Month, is not in order, it rather starts in April instead of January – GGw Nov 02 '17 at 14:08
@GGw Without (plus one) please. – Ilyes Nov 02 '17 at 14:10
@GGw If it doesn't answer your question, how can it be the correct query? – DavidG Nov 02 '17 at 14:12

score 1 · Accepted Answer · edited Nov 02 '17 at 14:12

1

Use the below code:

SELECT Res1.[Month]
         ,ISNULL(Res2.Amount,0)
  FROM #TempTable Res1
  LEFT JOIN
  (
  select DATENAME(MONTH,Res1.date) AS [Month]
         ,SUM(amount) AS Amount 
  from #Data Res1
  GROUP BY DATENAME(MONTH,Res1.date) 
  )Res2 ON Res2.[Month] = Res1.[Month]

edited Nov 02 '17 at 14:12

Ilyes

14,640
4
29
55

answered Nov 02 '17 at 14:06

Dheerendra

284
1
7

Please learn how to format your code. Also, this answer has the same issue as the other one. – DavidG Nov 02 '17 at 14:07

score 1 · Answer 3 · answered Nov 02 '17 at 14:12

update temptable from data table

update #TempTable
set amount = d.amount from ( select  datename(month, date) date
                                     , SUM(amount) amount from #Data
                                     group by datename(month, date)
                                     ) d inner join #TempTable t on t.month = d.date

                                     select * from #TempTable

Zorkolot · Answer 4 · 2017-11-02T17:37:01.873

One of your requirements is:

past dates with no amount will be 0

Your dataset didn't include this so I've added two additional rows in the example code below. I also added a different year to illustrate what happens with different years. I UNION ALL the values from #data with a CTE that select years, every month# of year, and 0. When SUM'ed, most of the 0 entries are eliminated, but months in the year without values in #Data remain with amount = 0. This result is subqueried to eliminate the 0 rows out of range of the min and max dates in #data.

create table #Data (date date, amount decimal(18,2))
insert into #Data(date,amount) values('2017-07-10',15.00)
insert into #Data(date,amount) values('2017-07-10',15.00)
insert into #Data(date,amount) values('2017-07-28',25.00)
insert into #Data(date,amount) values('2017-08-01',100.00)
insert into #Data(date,amount) values('2017-08-12',15.00)
insert into #Data(date,amount) values('2017-08-29',200.00)
insert into #Data(date,amount) values('2017-09-18',105.00)
insert into #Data(date,amount) values('2017-09-21',200.00)
insert into #Data(date,amount) values('2017-09-23',25.00)
insert into #Data(date,amount) values('2017-10-12',15.00)
insert into #Data(date,amount) values('2017-10-14',500.00)
insert into #Data(date,amount) values('2017-11-01',200.00)
insert into #Data(date,amount) values('2017-11-02',200.00)

--additional test values
insert into #Data(date,amount) values('2017-05-04',5.00)
insert into #Data(date,amount) values('2018-02-22',1.00)

DECLARE @minYear int =  (SELECT YEAR(MIN(date)) FROM #data)
       ,@minMonth int =  (SELECT MONTH(MIN(date)) FROM #data)
       ,@maxYear int =  (SELECT MAX(YEAR(date)) FROM #data)
       ,@maxMonth int =  (SELECT MONTH(MAX(date)) FROM #data);

WITH cteYear AS 
(
  SELECT @minYear AS num
  UNION ALL
  SELECT num + 1 FROM cteYear WHERE num + 1 <= @maxYear
)

SELECT dT.dateyear
      ,DateName( month , DateAdd( month , dT.datemonth, -1 ) ) AS [month]
      ,SUM(dT.amount) AS [Sum Amount]
  FROM (
         SELECT YEAR(date) AS [dateyear], MONTH(date) AS [datemonth], amount -- AS [SumAmount]     
           FROM #Data D       

         UNION ALL

         SELECT num, monthlist.month, 0 
           FROM cteYear CROSS JOIN (SELECT 1 AS [month] UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4  UNION ALL 
                                    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL 
                                    SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
                                   ) AS monthlist
       ) AS dT
WHERE (@minYear <> @maxYear AND dateyear = @minYear AND datemonth >= @minMonth)
      OR
      (@minYear <> @maxYear AND dateyear = @maxYear AND datemonth <= @maxMonth)
      OR
      (@minYear <> @maxYear AND dateyear <> @minYear AND dateyear <> @maxYear)
      OR
      (@minYear = @maxYear AND datemonth >= @minMonth AND datemonth <= @maxMonth)


GROUP BY dateyear, datemonth
ORDER BY dateyear, datemonth

Gives output:

dateyear    month      Sum Amount
2017        May        5.00
2017        June       0.00
2017        July       55.00
2017        August     315.00
2017        September  330.00
2017        October    515.00
2017        November   400.00
2017        December   0.00
2018        January    0.00
2018        February   1.00

Adding all the amounts per month using sql server

4 Answers4