# How to SUM DISTINCT by other columns?

I have a CTE that resulting something like this:

``````i x y
A 4 ...
A 4 ...
A 4 ...
A 4 ...
B 2 ...
B 2 ...
C 3 ...
``````

And already have a query that uses most of them:

``````WITH cte AS ( ... ) -- above is the example result of the cte
SELECT
COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count1
, COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count2
, SUM(x)/COUNT(*) bla_average
-- and much more similar to above
, -- how to get sum of A, B, C (4+2+3)
FROM cte
``````

How to get the SUM of `x` DISTINCT by `i` (4+2+3)?

Modified Fiddle

• dbfiddle.uk/… Commented Aug 25, 2020 at 22:35
• Your example CTE output shows that all `x` values for each separate `i` value are identical. Does this is strictly defined by CTE, or this is random, and there may be 2 or more different `x` per `i`? Commented Aug 26, 2020 at 5:06
• @Akina mostly identical but not always (so I decided to use MAX) Commented Aug 26, 2020 at 6:37
• but not always Imagine one of 4 rows with `i=A` have `x=5` - what result do you need in this case? 5+2+3? 4+5+2+3? Commented Aug 26, 2020 at 6:39

Nevermind, found a way, by adding one more CTE

``````WITH cte AS ( ... )
, sum1 AS (
SELECT i, MAX(x) tot -- or AVERAGE/MIN
FROM cte
GROUP BY 1
)
SELECT ...
, (SELECT SUM(tot) FROM sum1) AS sum1
FROM cte
``````
• You may obtain this MAX() in current CTE using window form of the aggregate function as additional column in each row, and do not use additional CTE. Commented Aug 26, 2020 at 6:50