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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How might I SUM values and project the totals?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 12/06/2002 :  12:21:12  Show Profile  Reply with Quote
Hi. I need to SUM the values of duplicate entries in my temp table, then project the results of these SUMs into a new temp table. Here's an illustration:

#Temptable1

ID VALUE
1 60
1 40
2 10
2 11.5
3 0
3 76


Again, I'd like to SUM values for each set of IDs and come up with single, summed records for each ID in a new temp table, like this:

#Temptable2

ID VALUE
1 100
2 21.5
3 76

Ideas?

thx

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 12/06/2002 :  12:24:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
INSERT INTO #temptable2 (ID, Value)
SELECT A.ID, SUM(A.Value) FROM #temptable1 A
INNER JOIN (SELECT ID FROM #temptable1 GROUP BY ID HAVING Count(*)>1) B
ON A.ID=B.ID
GROUP BY A.ID

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/06/2002 :  12:34:08  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
No need for the subquery ...

INSERT INTO #temptable2 (ID, Value)
SELECT A.ID, SUM(A.Value) FROM #temptable1 A
GROUP BY A.ID
HAVING COUNT(*) > 1

Note: the two posted solutions include records ONLY if there is more than 1; if there is just 1, it is not in the result set. That may or may not be what you need.

For all of them, regarless if there's 1 or more:

INSERT INTO #temptable2 (ID, Value)
SELECT A.ID, SUM(A.Value) FROM #temptable1 A
GROUP BY A.ID

- Jeff

(oops. .. i changed the WHERE to a HAVING in the first query....)

Edited by - jsmith8858 on 12/06/2002 12:56:36
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 12/06/2002 :  12:41:57  Show Profile  Reply with Quote
Thanks, all. I need to get up to speed on GROUP BY...

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