How to use PERCENTILE_CONT in GROUP BY query?

Question

I have a table named TabA with the following columns,

Id
Currency
Value
Institution

Expected Results:

Id  Currency  Cnt   Median(Value)   Institution

I am getting the values except Median(Value) using the below query,

SELECT  Id, Currency,Count(*) AS Cnt,
-- PERCENTILE_CONT(0.5) within GROUP (PARTITION BY Value) AS Median(Value) ,
MAX(Institution) AS Institution 
FROM  TabA
WHERE Institution like  '%Fed%' 
GROUP BY Id,Currency
ORDER BY Institution

when Include PERCENTILE_CONT, I am getting the following error.

The function PERCENTILE_CONT must have a WITHIN GROUP clause

I appreciate your help.

Answer 1

As already metioned in comments you need subquery for percentile_cont, then you can aggregate data:

with tmp as (
  select id, currency, institution,
         percentile_cont(0.5) within group (order by value) 
           over (partition by id, currency) AS mv
  from tabA WHERE Institution like  '%Fed%' )
SELECT Id, Currency, Count(*) AS Cnt, max(mv) as MedianValue,
       max(Institution) AS Institution 
FROM  tmp
GROUP BY Id, Currency
ORDER BY Institution

dbfiddle demo