Reputation: 3983
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
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
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