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.

You need a subquery [dbfiddle](https://dbfiddle.uk/uWjAW-uZ). It's not clear why you are grouping by Id and ordering by max(institution) which can be misleading. Isn't Id unique? Sample data and desired output are mandatory here. Similar question: [Unable to calculate median - SQL Server 2017](https://stackoverflow.com/questions/61412397/unable-to-calculate-median-sql-server-2017), but distinct won't work in this case. Subquery works. — Ponder Stibbons, Apr 28 '23 at 10:25
Thanks for providing the solution. Could you please add it in the answer section! — aka baka, Apr 28 '23 at 13:22

score 2 · Accepted Answer · answered Apr 28 '23 at 13:27

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

How to use PERCENTILE_CONT in GROUP BY query?

1 Answers1