Need Query Again - 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

Need Query Again

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2012-05-04 : 10:33:28

Sir ,

I have one other table ,

name age total sum
==================================
n 1 10 NULL
p 2 20 NULL
c 3 30 NULL
v 4 40 NULL

I need result like

name age total sum
==================================
n 1 10 10
p 2 20 30
c 3 30 60
v 4 40 100

Please help

Yaman

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 10:41:10

This is calculating a running total. That requires an ordering scheme. Assuming that age column is what you want to order it by, do the following:

First, run this query and inspect the results to see if the runningTotal column has the correct values.

;WITH cte AS
(
	SELECT a.*,runningTotal
	FROM YourTable a
	CROSS APPLY 
	(
		SELECT SUM(b.total) AS runningTotal FROM YourTable b
		WHERE b.age <= a.age
	) b
)
SELECT * FROM cte;

If you are satisfied that the results are correct, then run this query to update the table.

;WITH cte AS
(
	SELECT a.*,runningTotal
	FROM YourTable a
	CROSS APPLY 
	(
		SELECT SUM(b.total) AS runningTotal FROM YourTable b
		WHERE b.age <= a.age
	) b
)
UPDATE cte SET [sum] = runningTotal;

RL
Starting Member

15 Posts

Posted - 2012-05-04 : 12:46:19

Using [UPDATE...FROM] with correlated subquery:

UPDATE YT
SET [SUM] = (SELECT SUM(total) FROM YourTable
WHERE age <= YT.age)
FROM YourTable YT;

NOTE: Here's a correction of the aliasing issue in the previous solution:

;WITH cte AS
(
SELECT a.*, b.runningTotal
FROM YourTable a
CROSS APPLY
(
SELECT SUM(total) AS runningTotal
FROM YourTable
WHERE age <= a.age
) b
)
UPDATE cte SET [SUM] = runningTotal;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 13:49:54

quote:
NOTE: Here's a correction of the aliasing issue in the previous solution:

I didn't quite follow what the aliasing issue is/was. SQL Server is able to interpret it correctly. If anything, I would change it like this for readability:

;WITH cte AS
(
	SELECT a.*,X.runningTotal
	FROM YourTable a
	CROSS APPLY 
	(
		SELECT SUM(b.total) AS runningTotal FROM YourTable b
		WHERE b.age <= a.age
	) X
)
UPDATE cte SET [sum] = runningTotal;

RL
Starting Member

15 Posts

Posted - 2012-05-04 : 15:27:32

My mistake, don't know why I got an error the first time I ran it. Sorry about that!

RL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources