Sum of values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum of values

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 14:14:48

Hi,

I have this table

Cal Dates , Amount
2011-01-01 , 6
2011-01-02 , 6
2011-01-03 , 6
....
2011-02-01 , 6
2011-02-02 , 6
2011-02-03 , 6
...etc

I want to add a third column that will be the sum of the amount up until the specific date. For example, on 2011-01-03, the third column will have value 18 (6*3). On 2011-01-20, the third column will have value 120 (6*20).
The second condition is that this calculation resets on the first of each month and new calculation begins from the 1st of the new month. so for 2011-01-31 we should have the value $186, and on the 2011-02-01 the value should be 6 only, 2011-02-02, value is 12, etc.

Any ideas how I can achieve this? thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-22 : 14:25:34

SELECT Date,Amount,SUM(Amount) OVER( PARTITION BY DATEADD(month,datediff(month,0,date),0))

FROM yourTable

Jim

Everyday I learn something that somebody else already knew

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 14:39:07

Thanks Jim - however, your solution is summing up the 'amount' for the whole calendar month so the result looks like that:

180
180
180
180
180
...
186
186
186
186
...etc.

while I am looking for result that looks like that:

6
12
18
24
30
36
...
180
6
12
18
...
186
6
12
18
..,etc

So on the first of each month the calculation starts from the beginning and we adding one day at a time!

Please let me know your thoughts! thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 15:02:33

select dte, amt, tot = (select sum(t2.dte) from tbl t2 where t2.dte <= t.dte and datediff(mm,t.dte,t2.dte)=0)
from tbl t
order bt dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 15:04:32

Nigel - where does the second table come from?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 15:10:14

There isn't a second table - just one named tbl. Replace it with youe table name - similarly for column names.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 15:35:19

what is t and t2 then?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-22 : 15:53:26

quote:
Originally posted by VeselaApo

what is t and t2 then?

Those are aliases. Maybe it would be easier to see/read if the AS clause was used..?

select dte, amt, tot = (select sum(t2.dte) from tbl AS t2 where t2.dte <= t.dte and datediff(mm,t.dte,t2.dte)=0)
from tbl AS t
order bt dte

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 16:29:17

Ok this works perfectly!! thanks so much. The only problem is that I do not fully understand the logic behind this statement. Can you please explain:

1) What is the purpose of t2.dte <= t.dte
2) We use the datediff to start calculating from the beginning each new month, correct? How come in the calculation of the values after the first month the amounts from prior months are excluded?
thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 16:42:40

1. sums all the values in the table with date that is less than the current row
2. In the above the datediff excludes anything with a different month so you get all value for the current month and date less than the current date.

It does the calculation for each row in the resultset, Just consider what it does for a single row. That has a date and the subquery calculates based on that date.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-22 : 16:58:07

yes - i see now - thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources