| Author |
 Topic  |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 12/06/2002 : 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
USA
15559 Posts |
Posted - 12/06/2002 : 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
|
 |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 12/06/2002 : 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 |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 12/06/2002 : 12:41:57
|
Thanks, all. I need to get up to speed on GROUP BY...
|
|
|
| |
Topic |
|
How might I SUM values and project the totals?
