Adding SUM to Update Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding SUM to Update Query

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2011-01-19 : 11:44:27

Hey Friends!

I need some help please. Thanks so much to anyone who can assist in advance!

I have a Web page where a user can enter an ammount for an item. If the items have the same group number I want the UPDATE statement to Add them together.


UID   Item   GroupNo   Ammount   TotalAmt
1     A      1         $5.00     $5.00
2     B      2         $5.00     $7.00
3     C      2         $2.00     $7.00

That is what I want the finished to look like. When the user hits the web page it will have no Ammount and no Total. So they will only enter an AMMOUNT and I want the UPDATE statement to compute the totals.

Thanks again for any help! Soo much appreciated!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 12:35:17

select
uid,
item,
groupno,
ammount,
sum(ammount) over (partition by groupno) as TotalAmt
from
Your_Table

No, you're never too old to Yak'n'Roll if you're too young to die.

besadmin
Posting Yak Master

116 Posts

Posted - 2011-01-19 : 15:59:54

ahh im sorry. this is on a different database server than i originally thought. it is sql 2000. so that wont work will it?
Seems perfect if it was on our other server!

Thanks soo much for the reply!

Anything similar for SQL 2000 please?

Thanks Again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 16:52:46

[code]DECLARE @Sample TABLE
(
UID INT,
Item CHAR(1),
GroupNo INT,
Amount MONEY,
TotalAmt MONEY
)

INSERT @Sample
(
UID,
Item,
GroupNo,
Amount
)
SELECT 1, 'A', 1, $5.00 UNION ALL
SELECT 2, 'B', 2, $5.00 UNION ALL
SELECT 3, 'C', 2, $2.00

SELECT *
FROM @Sample

-- Do the update SQL 2000 style
UPDATE s
SET s.TotalAmt = w.Total
FROM @Sample AS s
INNER JOIN (
SELECT GroupNo,
SUM(Amount) AS Total
FROM @Sample
GROUP BY GroupNo
) AS w ON w.GroupNo = s.GroupNo

SELECT *
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 16:58:10

[code]--UID Item GroupNo Ammount TotalAmt
--1 A 1 $5.00 $5.00
--2 B 2 $5.00 $7.00
--3 C 2 $2.00 $7.00

--create table ttt(uid int, item char(1), groupno int, ammount int)
--insert ttt
--select 1,'a',1,5 union all
--select 2,'b',2,5 union all
--select 3,'c',2,2

select * from ttt

select t1.uid,t1.item,t1.groupno,t1.ammount,dt.TotalAmt
from ttt as t1
join
(select groupno,sum(ammount) as TotalAmt from ttt group by groupno)dt
on dt.groupno=t1.groupno[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 16:59:53

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources