0

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:

enter image description here

What am i doing wrong? Can anyone assist?

Improve this question
4
  • you missed the JOIN condition a.[Month/Year] = b.[Month/Year] Commented Nov 7, 2019 at 9:19
  • @Squirrel what do you mean? I don't get it Commented Nov 7, 2019 at 9:28
  • ON a.[Month/Year] = b.[Month/Year] AND a.[date] >= b.[date] Commented Nov 7, 2019 at 9:36
  • not working at all Commented Nov 7, 2019 at 9:42

1 Answer 1

Reset to default
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'
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I'm getting this error 'Incorrect syntax near 'order'.'
@Honestman dbfiddle.uk/… check this fiddle
OP is using SQL Server 2012
@Squirrel dbfiddle.uk/… check sql server 2012
my apology . . i must have got the version mix-up in my head
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.