SQL Server How to use PERCENTILE_CONT in GROUP BY query?

6yoyoihd 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(163)

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.

sql-server

来源：https://stackoverflow.com/questions/76126235/how-to-use-percentile-cont-in-group-by-query

1条答案

按热度按时间

ggazkfy81#

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

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server How to use PERCENTILE_CONT in GROUP BY query?

1条答案

相关问题

热门标签

最新问答