Update table with sum of rows from the same table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update table with sum of rows from the same table

Author

Topic

bismarkcount
Starting Member

15 Posts

Posted - 2009-01-13 : 15:06:15

I want to update a table with the sum of some other rows in the same table. example:
I want to update 1t in 2008 with the sum of all key 1 in 2008, the 2t with the sum of all key 2 in 2008, etc...
the same for 2009.

how would it be the statement to do this?

year key amnt
2008 1 50
2008 1 45
2008 2 60
2008 3 55
2008 1t 0
2008 2t 0
2008 3t 0
2009 1 40
2009 1 30
2009 2 55
2009 1t 0
2009 2t 0

any suggestions??
thanx in advance

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-13 : 15:13:47

[CODE]
UPDATE T
SET T.AMNT = A.AMNT
FROM MY_TABLE T
JOIN (SELECT YEAR,
(CASE KEY
WHEN 1 THEN '1t'
WHEN 2 THEN '2t'
WHEN 3 THEN '3t'
END) CONV_KEY,
SUM(AMNT) AS AMNT
FROM MY_TABLE
GROUP BY YEAR,(CASE KEY
WHEN 1 THEN '1t'
WHEN 2 THEN '2t'
WHEN 3 THEN '3t'
END)) A
ON T.YEAR = A.YEAR
AND T.KEY = A.CONV_KEY
[/CODE]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-13 : 15:18:52

It's a terrible design to store sums and values in the same column of a table.

bismarkcount
Starting Member

15 Posts

Posted - 2009-01-13 : 15:23:38

rohitkumar: COOL QUERY

Skorch: THNX... YOU'RE RIGHT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources