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

Question

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?

Answer 1

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

Answer 2

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;