How to obtain the median of a table column using T-SQL query?

Question

Kindly help with the following T-SQL query. The data used is that of us_counties_2010.

SELECT 
    SUM(P0010001) AS 'County Sum',
    ROUND(AVG(P0010001),0) AS 'County Average',
    PERCENTILE_CONT(0.5)
    WITHIN GROUP (ORDER BY P0010001) OVER() AS 'County Median'
FROM
    USCounties;

Messages

Column 'USCounties.P0010001' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried executing multiple times by making minor alterations.

Answer 1

Try this :

The sum() need group by or over() to work, and the round is not a window function.

SELECT SUM(P0010001) OVER() AS 'County Sum',
      ROUND(AVG(P0010001) OVER(), 0) AS 'County Average',
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY P0010001) OVER() AS 'County Median'
FROM USCounties;

Answer 2

You need to include the group by clause so it understand how to show the data.

SELECT 
    SUM(P0010001) AS 'County Sum',
    ROUND(AVG(P0010001),0) AS 'County Average',
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY P0010001) OVER() AS 'County Median'
FROM USCounties
GROUP BY UsCounties.P0010001;

fiddle

When using an aggregate function (sum, avg, count, etc.) a query will fail because the aggregate functions require grouping of data to calculate their values. When an aggregate function is used in a query without group by the db engine doesn't know how to group the data and calculate the aggregate value and it throws an error. In your original query, the percentile_cont function from the within group (order by P0010001) clause computes the median value for each country in the table ordered by the P0010001 column. The Over clause specifies that this median value should be calculated for all rows in the table rather than being limited to a specific group.