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.
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:#Temptable1ID VALUE1 601 402 102 11.53 03 76Again, 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:#Temptable2ID VALUE1 1002 21.53 76Ideas?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 AINNER JOIN (SELECT ID FROM #temptable1 GROUP BY ID HAVING Count(*)>1) BON A.ID=B.IDGROUP BY A.ID |
|
|
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(*) > 1Note: 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 |
|
|
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... |
|
|
|
|
|
|
|