SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using Loops?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

55 Posts

Posted - 05/31/2012 :  17:18:32  Show Profile  Reply with Quote
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

USA
15559 Posts

Posted - 05/31/2012 :  17:34:15  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

kotonikak
Yak Posting Veteran

55 Posts

Posted - 05/31/2012 :  17:46:24  Show Profile  Reply with Quote
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?
Go to Top of Page

kotonikak
Yak Posting Veteran

55 Posts

Posted - 05/31/2012 :  17:47:05  Show Profile  Reply with Quote
By the way...thank you for your reply!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts

Posted - 05/31/2012 :  17:49:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

kotonikak
Yak Posting Veteran

55 Posts

Posted - 05/31/2012 :  18:05:30  Show Profile  Reply with Quote
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?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts

Posted - 05/31/2012 :  18:14:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
Don't put the ORDER BY in the CTE definition, just put it after the SELECT.
Go to Top of Page

kotonikak
Yak Posting Veteran

55 Posts

Posted - 05/31/2012 :  18:15:24  Show Profile  Reply with Quote
thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000