I'm trying to sum values from duplicates rows (with the same ID, Month and Person) in multiple columns to the first or the last duplicate row. Then delete the duplicate rows exept the one with the total value. The biggest problem is that sometimes I need to sum values in two different columns.
PrimaryTable:
ID Month Person Value1 Value2
**123 1 Smith** 10 20
**123 1 Smith** 5 NULL
**123 1 Smith** NULL 5
123 2 Smith 10 5
**189 3 Murphy** NULL 15
**189 3 Murphy** NULL 10
190 2 Brown 25 25
**345 2 Lee** 25 20
**345 2 Lee** 25 20
Result1 (expected result after sum duplicates values to the first one):
ID Month Person Value1 Value2
123 1 Smith **15** **25**
123 1 Smith 5 NULL
123 1 Smith NULL 5
123 2 Smith 10 5
189 3 Murphy NULL **25**
189 3 Murphy NULL 10
190 2 Brown 25 25
345 2 Lee **50** **40**
345 2 Lee 25 20
FinalTable (expected result after deleting duplicates, except the first one):
ID Month Person Value1 Value2
123 1 Smith **15** **25**
123 2 Smith 10 5
189 3 Murphy NULL **25**
190 2 Brown 25 25
345 2 Lee **50** **40**
I'm trying with this code:
SELECT ID, Month, Person, SUM(Value1), SumValue2
FROM
(
SELECT ID, Month, Person, Value1, SUM(Value2) AS SumValue2
FROM db.Hours
GROUP BY ID, Month, Person, Value1
)
GROUP BY ID, Month, Person, SumValue2
But sometimes it makes double sum of total of Value2.