Adding up rows up to a certain value

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

Adding up rows up to a certain value

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-04 : 16:03:03

I have the following data:

LAmount seq IIntegral
6087 1 69640
2605 2 2970
2308 3 200
2278 4 2540
1770 5 2840
1060 6 1153.333333
714 7 10722.85714
-3039 8 2287.5
-3954 9 6668.888889
-6955 10 1134
-7522 11 2027.272727
-8637 12 713.3333333
-9065 13 3872.307692
-11582 14 2524.285714
-13349 15 1373.333333
-14379 16 3933.75
-17526 17 240
-17730 18 3726.666667
-21084 19 2684.210526

and want the following column:

Integral
48441.73936 (Sum of IIntegral from seq 4 - seq 19)
48441.73936 (Sum of IIntegral from seq 4 - seq 19)
48441.73936 (Sum of IIntegral from seq 4 - seq 19)
48441.73936 (Sum of IIntegral from seq 4 - seq 19)
45901.73936 (Sum of IIntegral from seq 5 - seq 19)
43061.73936 (Sum of IIntegral from seq 6 - seq 19)
41908.40603 (Sum of IIntegral from seq 7 - seq 19)
31185.54888 so on...
28898.04888
22229.15999
21095.15999
19067.88727
18354.55393
14482.24624
11957.96053
10584.62719
6650.877193
6410.877193
2684.210526

Basically, I want it to recognize that if the sequence is greater than or equal to 4, to do the summation of IIntegral from that sequence all the way to the last sequence, and if it is less than 4, to only do the sum of sequence 4 to 19 (it's supposed to be 20 but i have to fix the code...this shouldn't affect anything for this question)

First, I have the following cte to recognize the 4th sequence (or any sequence depending on the number of rows and the percentile)...right now @row = 20 and @VaR=80.00

with d as
(
select LAmount, seq = row_number() over (order by LAmount desc) from dbo.Data2
)
,VaRVariable as
(
select LAmount
from d
where seq = @row-(@row*(@VaR/100))
)
select * from VaRVariable

I also have an older code which actually did the summation of IIntegral starting at sequence 1-19, sequence 2-19, sequence 3-19 and so on...but now depending on the sequence I get from the formula above, I want it to get the column I mentioned above. Here is the older code (part of the cte above):

,temp4 as
(
select sum(b.IIntegral) as Integral
from temp3 a CROSS JOIN temp3 b
where a.seq<=b.seq
group by a.seq
)

which yielded the following column:

Integral
121251.7394
51611.73936
48641.73936
48441.73936
45901.73936
43061.73936
41908.40603
31185.54888
28898.04888
22229.15999
21095.15999
19067.88727
18354.55393
14482.24624
11957.96053
10584.62719
6650.877193
6410.877193
2684.210526

Do I have to use an if statement to do what I want or is there an easier way to approach this?

Any help would be greatly appreciated! (Sorry if what I'm asking is confusing)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 16:27:09

[code]
SELECT t.*,t1.AccSum
FROM table t
CROSS APPLY (SELECT SUM(IIntegral) AS AccSum
FROM table
WHERE (seq >= t.Seq
OR seq <=4)
AND seq <=19
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-05 : 03:26:53

quote:
Originally posted by visakh16

SELECT t.*,t1.AccSum
FROM table t
CROSS APPLY (SELECT SUM(IIntegral) AS AccSum
FROM table
WHERE (seq >= t.Seq
OR seq <=4)
AND seq <=19
)t1

Nice one Visakh.
Very compact.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:23:26

Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-05 : 12:01:28

I tried your code and it didn't work properly. I got the following columns:

seq AccSum
19 78034.21053
18 81760.87719
17 82000.87719
16 85934.62719
15 87307.96053
14 89832.24624
13 93704.55393
12 94417.88727
11 96445.15999
10 97579.15999
9 104248.0489
8 106535.5489
7 117258.406
6 118411.7394
5 121251.7394
4 121251.7394
3 121251.7394
2 121251.7394
1 121251.7394

However, I did update my old code to the following and produced closer results to what I want:

select min(a.seq) as seq,
sum(b.IIntegral) as AccSum
from temp3 a CROSS JOIN temp3 b
where (a.seq<=b.seq or a.seq<=(4-1) and a.seq<=19)
group by a.seq

and this gave me the following results:

seq AccSum
1 121251.739
2 121251.739
3 121251.739
4 48441.73936
5 45901.73936
6 43061.73936
7 41908.40603
8 31185.54888
9 28898.04888
10 22229.15999
11 21095.15999
12 19067.88727
13 18354.55393
14 14482.24624
15 11957.96053
16 10584.62719
17 6650.877193
18 6410.877193
19 2684.210526

which is very close to what I want...the only thing is that the first 3 rows (seq 1-3) are adding the IIntegral values from seq 1-19 instead of seq 4-19...so now i'm stuck..

Is there any way to update your old code or mine to make this work properly? I've been looking at it and changing things around for a couple hours and don't know what to do....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:29:12

what about this?


SELECT t.*,t1.AccSum
FROM table t
CROSS APPLY (SELECT SUM(IIntegral) AS AccSum
             FROM table
             WHERE seq >= CASE WHEN t.Seq < 4 THEN 4 ELSE t.Seq END
             AND seq <=19
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-05 : 14:20:58

that did the job. thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 14:43:06

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-15 : 12:11:29

Is there any way to do this problem without using CROSS APPLY? It works perfectly it's just that I have a database with hundreds of thousands of rows and CROSS APPLY just takes A LOT of time...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-15 : 14:02:15

Here's a variation on my earlier reply that doesn't use CROSS APPLY:

SELECT a.seq, SUM(CASE WHEN b.seq>=4 THEN b.IIntegral END) integral
FROM table a CROSS JOIN @table b 
WHERE a.seq<=b.seq
GROUP BY a.seq
ORDER BY a.seq DESC

The plan is very similar to visakh's query, possibly worse but needs testing. Of course if you could upgrade to SQL 2012:

SELECT *, SUM(CASE WHEN seq>=4 THEN IIntegral END) OVER (ORDER BY Seq DESC) FROM table

This is a significantly better plan, it has no loops or joins.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 15:43:08

quote:
Originally posted by robvolk

Here's a variation on my earlier reply that doesn't use CROSS APPLY:
SELECT a.seq, SUM(CASE WHEN b.seq>=4 THEN b.IIntegral END) integral
FROM table a CROSS JOIN @table b
WHERE a.seq<=b.seq
GROUP BY a.seq
ORDER BY a.seq DESC
The plan is very similar to visakh's query, possibly worse but needs testing. Of course if you could upgrade to SQL 2012:
SELECT *, SUM(CASE WHEN seq>=4 THEN IIntegral END) OVER (ORDER BY Seq DESC) FROM table
This is a significantly better plan, it has no loops or joins.

why?
wont it work in sql 2005/2008? I think it should work from 2005 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-15 : 15:54:43

Nope, ORDER BY in the OVER clause is not supported in aggregate functions prior to 2012. You get a syntax error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 15:57:05

quote:
Originally posted by robvolk

Nope, ORDER BY in the OVER clause is not supported in aggregate functions prior to 2012. You get a syntax error.

oh ok
I missed that part
Thought its PARTITION BY
sorry about that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-18 : 15:23:01

This problem is working just fine if I have the simple data above but I seem to be running into issues with this problem when i change the data around. In my previous example, I only had one sequence pertaining to one IIntegral value and that's it. I am now expanding my data to include these numbers (sample numbers):

seq RowID ColID IIntegral
1 01 A 5
1 02 B 5
1 03 C 5
2 01 A 9
2 02 B 9
2 03 C 9
3 01 A 8
3 02 B 8
3 03 C 8

Because I have a different RowID and ColID at a specific sequence number, when I take the integral (from the code using CROSS APPLY) I get the following column (If i'm summing everything up and not up to a certain point):

Integral
66
66
66 (5+5+5+9+9+9+8+8+8)
51
51
51 (9+9+9+8+8+8)
24
24
24 (8+8+8)

Basically, it's taking the sum of the IIntegral at that sequence number and adding all those together and so on....that's not what I want.

I want:
Integral:
22
22
22 (5+9+8)
17
17
17 (9+8)
8
8
8 (8)

Is there any way for sql to recognize that I only want that one IIntegral number to add up and not all the values at that specific sequence number?

sorry again if this is confusing...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-18 : 15:45:34

[code]DECLARE @table TABLE(seq INT, RowID CHAR(2), ColID CHAR(1), IIntegral FLOAT)
INSERT @table SELECT
1,'01','A',5 UNION ALL SELECT
1,'02','B',5 UNION ALL SELECT
1,'03','C',5 UNION ALL SELECT
2,'01','A',9 UNION ALL SELECT
2,'02','B',9 UNION ALL SELECT
2,'03','C',9 UNION ALL SELECT
3,'01','A',8 UNION ALL SELECT
3,'02','B',8 UNION ALL SELECT
3,'03','C',8

SELECT t.*,t1.AccSum
FROM @table t
CROSS APPLY (SELECT SUM(IIntegral) AS AccSum
FROM @table
WHERE ColID=t.ColID AND RowID=t.RowID
AND seq >= t.Seq
)t1[/code]

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-19 : 09:50:49

I did try to do something like that earlier but it was taking forever to execute....my data right now has 10,200 rows and new data will have about 5,100,000 rows...I am trying to execute the method and it's been 13 minutes and still hasn't finished. Is there ANY possible way to not use any cross apply or cross joins?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-19 : 09:54:05

10,000 rows should not take that long. I would suggest checking for missing indexes.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-19 : 10:03:28

You can try a cursor.

Or upgrade to SQL Server 2012.

There is a quirky behavior with the UPDATE statement that you might be able to use:

http://www.sqlteam.com/article/creating-a-sequential-record-number-field
http://consultingblogs.emc.com/jamiethomson/archive/2006/02/28/3001.aspx

The problem is it's not standard or consistent behavior. I've used it several times when left with no choice, but it requires clustering the data a certain way and even then it's not guaranteed to work correctly. (I always use a temp table, not a table variable, so I can force a clustered index on it)

Another option is a CLR function:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-yet-again-sqlclr-saves-the-day.aspx

Dare I suggest (again) that SQL Server is not really the best tool for this kind of thing? It's not a general purpose programming language. This is a running total and most properly resides in a reporting layer that is designed to do such things.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 14:12:48

quote:
Originally posted by kotonikak

I did try to do something like that earlier but it was taking forever to execute....my data right now has 10,200 rows and new data will have about 5,100,000 rows...I am trying to execute the method and it's been 13 minutes and still hasn't finished. Is there ANY possible way to not use any cross apply or cross joins?

check execution plan and identify costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources