Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
15732 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  
 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.07 seconds. Powered By: Snitz Forums 2000