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?
Just figured it out... gotta drop the group by and give both aggregation functions a over statement.
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).