Update a table with sums from this 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

General SQL Server Forums

New to SQL Server Programming

Update a table with sums from this table

Author

Topic

leeno
Starting Member

1 Post

Posted - 2006-08-14 : 12:11:15

Hi,
I need to update a table by summing the amount for a year and month with the month that comes before.
Table Ex:
Year-Month-Amount...
2006-01-40
2006-02-10

We're trying to optimize a series of existing code that calculates the cumalated stats for each month. (Once we've calculated the amount for February we then need to add the amount from January and so on.)

The query that we tried was something like this:
UPDATE table1
SET amount =
(SELECT SUM(amount) FROM table1 WHERE year="2006" AND month="01")
WHERE year="2006" AND month="02"

This didn't work. Any ideas???

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 12:14:59

Probably
UPDATE table1
SET amount =
(SELECT SUM(amount) FROM table1 WHERE year=2006 AND month=01)
WHERE year=2006 AND month=02

or
UPDATE table1
SET amount =
(SELECT SUM(amount) FROM table1 WHERE year='2006' AND month='01')
WHERE year='2006' AND month='02'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 12:19:22

>> This didn't work ?
Means what ? Any errors ? Not updated ? Not correctly updated ?
Also note that u cannot use double quotes as in "2000", instead use single quotes as '2000' or if it is integer field, do not use quotes.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources