CODEWITHSUNDEEP
sql-servert-sqlgroup-by
John Chrysostom
John Chrysostom

Reputation: 3983

Use percentile_cont with a "group by" statment in T-SQL

I'd like to use the percentile_cont function to get median values in T-SQL. However, I also need to get mean values as well. I'd like to do something like the following:

SELECT  CustomerID ,
    AVG(Expenditure) AS MeanSpend , percentile_cont
    ( .5) WITHIN GROUP(ORDER BY Expenditure) OVER( ) AS MedianSpend
FROM    Customers
GROUP BY CustomerID

Can this be accomplished? I know I can use the OVER clause to group the percentile_cont results...

but then I'm stuck using two queries, am I not?

Upvotes: 19

Views: 42370

Answers (2)

jumxozizi
jumxozizi

Reputation: 649

You can't use "group by" with window functions. These functions return the aggregated values for every row. One way is to use "select distinct" to get rid of the duplicate rows. Just make sure you partition each window function by the non-aggregated columns (groupId in this example).

--Generate test data
SELECT  TOP(10) 
    value.number%3  AS  groupId
,   value.number    AS  number
INTO    #data
FROM  master.dbo.spt_values  AS  value
WHERE value."type" = 'P' 
ORDER BY NEWID()
;

--View test data
SELECT  * FROM #data ORDER BY groupId,number;

--CALCULATE MEDIAN
SELECT DISTINCT 
    groupId
,   AVG(number)                                         OVER(PARTITION BY groupId)  AS mean
,   percentile_cont(.5) WITHIN GROUP(ORDER BY number)   OVER(PARTITION BY groupId)  AS median
FROM    #data
;

--Clean up
DROP TABLE #data;

Upvotes: 9

John Chrysostom
John Chrysostom

Reputation: 3983

Just figured it out... gotta drop the group by and give both aggregation functions a over statement.

SELECT CustomerID,
    AVG(Expenditure) OVER(PARTITION BY CustomerID) AS MeanSpend,
    percentile_cont(.5) WITHIN GROUP(ORDER BY Expenditure) OVER(PARTITION BY CustomerID) AS MedianSpend
FROM Customers

Upvotes: 20

Related Questions