SQL Server 2012 running total by date

Question

I have a table that contains column [Month/Year], [Date], [Volume]

When i run this query "select [Month/Year], [date], sum([Volume]) as 'sum' from VOLUME where [Month/Year] = '2018-10-01' group by [Month/Year], [date] order by [date]", it will show this:

    [Month/Year]   [Date]   [Volume]
    2018-10-01  2018-10-15  2000
    2018-10-01  2018-10-16  4000
    2018-10-01  2018-10-17  7000
    2018-10-01  2018-10-18  6000
    2018-10-01  2018-10-19  7000
    2018-10-01  2018-10-20  7000
    2018-10-01  2018-10-21  8000
    2018-10-01  2018-10-22  9000
    2018-10-01  2018-10-23  11000
    2018-10-01  2018-10-24  10000
    2018-10-01  2018-10-25  13000
    2018-10-01  2018-10-26  12000
    2018-10-01  2018-10-27  8000
    2018-10-01  2018-10-28  8000
    2018-10-01  2018-10-29  9000
    2018-10-01  2018-10-30  8000

When i run this query, it give me this output which is wrong:

select a.[date], SUM(b.[Volume]) as barge_sum
from VOLUME a
INNER JOIN VOLUME b ON a.[date] >= b.[date]
where a.[Month/Year] = '2018-10-01'
group by a.[date]
order by a.[date]

Output:

2018-10-15  4000
2018-10-16  24000
2018-10-17  91000
2018-10-18  114000
2018-10-19  182000
2018-10-20  231000
2018-10-21  328000
2018-10-22  450000
2018-10-23  671000
2018-10-24  710000
2018-10-25  1092000
2018-10-26  1152000
2018-10-27  832000
2018-10-28  896000
2018-10-29  1089000
2018-10-30  1032000

The output should be cumulative in this manner:

What am i doing wrong? Can anyone assist?

you missed the JOIN condition `a.[Month/Year] = b.[Month/Year]` — Squirrel, Nov 07 '19 at 09:19
`ON a.[Month/Year] = b.[Month/Year] AND a.[date] >= b.[date]` — Squirrel, Nov 07 '19 at 09:36

Zaynul Abadin Tuhin · Answer 1 · 2019-11-07T09:18:09.787

1

you can try like below by using window function sum()

select a.[date], SUM(a.[Volume]) over(order by  a.[date]) as barge_sum
from VOLUME a
where a.[Month/Year] = '2018-10-01'

edited Nov 07 '19 at 09:18

answered Nov 07 '19 at 09:15

Zaynul Abadin Tuhin

31,407
5
33
63

I'm getting this error 'Incorrect syntax near 'order'.' – Honestman Nov 07 '19 at 09:18
@Honestman https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=00b545eaf403fc12282d623ad69960b5 check this fiddle – Zaynul Abadin Tuhin Nov 07 '19 at 09:18
OP is using `SQL Server 2012` – Squirrel Nov 07 '19 at 09:19
@Squirrel https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=00b545eaf403fc12282d623ad69960b5 check sql server 2012 – Zaynul Abadin Tuhin Nov 07 '19 at 09:20
1

my apology . . i must have got the version mix-up in my head – Squirrel Nov 07 '19 at 09:21
@Squirrel its ok mate – Zaynul Abadin Tuhin Nov 07 '19 at 09:22
@ZaynulAbadinTuhin Thanks but how do i apply ur query to my? – Honestman Nov 07 '19 at 09:27
@Honestman i already did for your one in my answer – Zaynul Abadin Tuhin Nov 07 '19 at 09:29

SQL Server 2012 running total by date

1 Answers1