CODEWITHSUNDEEP
sqlsql-serversql-server-2008
PBMe_HikeIt
PBMe_HikeIt

Reputation: 679

SQL Server Sum multiple rows into one - no temp table

I would like to see a most concise way to do what is outlined in this SO question: Sum values from multiple rows into one row that is, combine multiple rows while summing a column.

But how to then delete the duplicates. In other words I have data like this:

Person  Value
--------------
1         10
1         20
2         15

And I want to sum the values for any duplicates (on the Person col) into a single row and get rid of the other duplicates on the Person value. So my output would be:

Person  Value
-------------
1        30
2        15

And I would like to do this without using a temp table. I think that I'll need to use OVER PARTITION BY but just not sure. Just trying to challenge myself in not doing it the temp table way. Working with SQL Server 2008 R2

Simply put, give me a concise stmt getting from my input to my output in the same table. So if my table name is People if I do a select * from People on it before the operation that I am asking in this question I get the first set above and then when I do a select * from People after the operation, I get the second set of data above.

Upvotes: 1

Views: 1403

Answers (2)

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

Not sure why not using Temp table but here's one way to avoid it (tho imho this is an overkill):

UPDATE MyTable SET VALUE = (SELECT SUM(Value) FROM MyTable MT WHERE MT.Person = MyTable.Person);

WITH DUP_TABLE AS
   (SELECT ROW_NUMBER()
    OVER (PARTITION BY Person ORDER BY Person) As ROW_NO
    FROM MyTable)
DELETE FROM DUP_TABLE WHERE ROW_NO > 1;

First query updates every duplicate person to the summary value. Second query removes duplicate persons.

Demo: http://sqlfiddle.com/#!3/db7aa/11

Upvotes: 2

domdomcodecode
domdomcodecode

Reputation: 2453

All you're asking for is a simple SUM() aggregate function and a GROUP BY

SELECT Person, SUM(Value)
FROM myTable
GROUP BY Person

The SUM() by itself would sum up the values in a column, but when you add a secondary column and GROUP BY it, SQL will show distinct values from the secondary column and perform the aggregate function by those distinct categories.

Upvotes: 0

Related Questions