Search code examples
sqlsumdistinct

SUM a column in SQL, based on DISTINCT values in another column, GROUP BY a third column

I'd appreciate some help on the following SQL problem:

I have a table of 3 columns:

ID    Group    Value
1     1        5
1     1        5
1     2        10    
1     2        10
1     3        20
2     1        5
2     1        5
2     1        5
2     2        10
2     2        10
3     1        5
3     2        10
3     2        10
3     2        10
3     4        50

I need to group by ID, and I would like to SUM the values based on DISTINCT values in Group. So the value for a group is only accounted for once even though it may appear multiple for times for a particular ID.

So for IDs 1, 2 and 3, it should return 35, 15 and 65, respectively.

ID    SUM
1     35
2     15
3     65

Note that each Group doesn't necessarily have a unique value

Thanks

Solution

  • Given what we know at the moment this is what I'm thinking...

    The CTE/Inline view eliminate duplicates before the sum occurs.

    WITH CTE AS (SELECT DISTINCT ID, Group, Value FROM TableName)
SELECT ID, Sum(Value)
FROM CTE
GROUP BY ID

    or

    SELECT ID, Sum(Value)
FROM (SELECT DISTINCT * FROM TableName) CTE
GROUP BY ID