0

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

7
  • 2
    Please, use this fiddle to show us an example of your query. Edit your question and add a link to your fiddle.
    – McNets
    Commented Aug 25, 2020 at 22:15
  • 1
    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?
    – Akina
    Commented Aug 26, 2020 at 5:06
  • @Akina mostly identical but not always (so I decided to use MAX)
    – Kokizzu
    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?
    – Akina
    Commented Aug 26, 2020 at 6:39

1 Answer 1

0

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
1
  • 1
    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.
    – Akina
    Commented Aug 26, 2020 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.