Using Loops? - 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

Using Loops?

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-31 : 17:18:32

Given the following data, I am having a problem figuring out how to sum up certain values in the IIntegral column based on the rows of the seq column.

seq Denominator IIntegral
19 0.95 2684.21052631579
18 0.9 3726.66666666667
17 0.85 240
16 0.8 3933.75
15 0.75 1373.33333333333
14 0.7 2524.28571428571
13 0.65 3872.30769230769
12 0.6 713.333333333333
11 0.55 2027.27272727273
10 0.5 1134
9 0.45 6668.88888888889
8 0.4 2287.5
7 0.35 10722.8571428571
6 0.3 1153.33333333333
5 0.25 2840
4 0.2 2540
3 0.15 200
2 0.1 2970
1 0.05 69640

What I want is a new column that takes the sum of the IIntegral based on the seq column. So I want the following column (numbers may be a little off because I am using excel and it is rounding):

Integral
2,684.210 (SUM of IIntegral at seq 19)
6,411 (SUM of IIntegral at seq 19 and 18)
6,651 (SUM of IIntegral at seq 19, 18, and 17)
10,586 ...so on
11,959
14,483
18,357
19,068
21,096
22,233
28,901
31,185
41,912
43,066
45,905
48,446
48,646
51,613
121,265 (SUM of IIntegral value at seq 19-seq1)

I was wondering what the easiest way of doing this is, whether to use a loop or something else? I am new to SQL 2008 but if someone could point me in the right direction I would really appreciate it.

Thank you so much.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-31 : 17:34:15

Setup:

DECLARE @t TABLE(seq INT NOT NULL, Denominator FLOAT NOT NULL, IIntegral FLOAT NOT NULL)
INSERT @t SELECT 
19,0.95,2684.21052631579 UNION ALL SELECT 
18,0.9,3726.66666666667 UNION ALL SELECT 
17,0.85,240 UNION ALL SELECT 
16,0.8,3933.75 UNION ALL SELECT 
15,0.75,1373.33333333333 UNION ALL SELECT 
14,0.7,2524.28571428571 UNION ALL SELECT 
13,0.65,3872.30769230769 UNION ALL SELECT 
12,0.6,713.333333333333 UNION ALL SELECT 
11,0.55,2027.27272727273 UNION ALL SELECT 
10,0.5,1134 UNION ALL SELECT 
9,0.45,6668.88888888889 UNION ALL SELECT 
8,0.4,2287.5 UNION ALL SELECT 
7,0.35,10722.8571428571 UNION ALL SELECT 
6,0.3,1153.33333333333 UNION ALL SELECT 
5,0.25,2840 UNION ALL SELECT 
4,0.2,2540 UNION ALL SELECT 
3,0.15,200 UNION ALL SELECT 
2,0.1,2970 UNION ALL SELECT 
1,0.05,69640

Query:

SELECT SUM(b.IIntegral) integral
FROM @t a CROSS JOIN @t b 
WHERE a.seq<=b.seq
GROUP BY a.seq
ORDER BY a.seq DESC

SQL 2012 version:

SELECT SUM(IIntegral) OVER (ORDER BY seq DESC ROWS BETWEEN unbounded preceding AND CURRENT ROW) Integral FROM @t

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-31 : 17:46:24

If I already have a temporary table with all the values (temp3) which is included in a CTE, is there any way I can do this procedure in the CTE?

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-31 : 17:47:05

By the way...thank you for your reply!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-31 : 17:49:13

Yes, just change the references to @t to match your CTE name and put the SELECT after your CTE definition.

By the way, if this is for a report, almost every reporting package can do running totals, it's best to do them there rather than in a query.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-31 : 18:05:30

Thank you, I figured and no it's not for a report. Also, is there any other way to order a.seq by descending value since ORDER BY is not valid in CTEs?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-31 : 18:14:14

Don't put the ORDER BY in the CTE definition, just put it after the SELECT.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-31 : 18:15:24

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources