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 2000 Forums
 SQL Server Development (2000)
 How might I SUM values and project the totals?

Author  Topic 

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-06 : 12:21:12
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

15732 Posts

Posted - 2002-12-06 : 12:24:45
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

7423 Posts

Posted - 2002-12-06 : 12:34:08
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 - 2002-12-06 : 12:41:57
Thanks, all. I need to get up to speed on GROUP BY...

Go to Top of Page
   

- Advertisement -