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

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.

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

Thanks

CodePudding user response：

the CTE will remove all duplicates, so if there a sdiffrenet values for ID and Group, it will be counted.

The next SELECT wil "GROUP By" ID

For Pstgres you would get

WITH CTE as
(SELECT DISTINCT "ID", "Group", "Value" FROM tablA
)
SELECT "ID", SUM("Value") FROM CTE GROUP BY "ID"
ORDER BY "ID"

ID | sum
-: | --:
 1 |  35
 2 |  15
 3 |  65

db<>fiddle here

CodePudding user response：

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

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