CODEWITHSUNDEEP
sql-servert-sqlgroup-bypercentile-cont
aka baka
aka baka

Reputation: 223

How to use PERCENTILE_CONT in GROUP BY query?

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.

Upvotes: 0

Views: 857

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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

Upvotes: 2

Related Questions