WITHIN GROUP clause how does it work in this percentile function-CodePudding

SELECT 
    sum(p0010001) AS "County Sum",
    round(avg(p0010001), 0) AS "County Average",
    percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001) AS "County Median"
FROM us_counties_2010;

i am unable to get what is WITHIN GROUP clause is doing in this PostgreSQL aggregate function.

p0010001 is Total population of particular state and data type integer

CodePudding user response：

I think you are looking for some reason or sense here that does not exist. The whole construct WITHIN GROUP (ORDER BY ...) is just the magic phrase that makes the percentile functions run. I don't think you can pick it apart and understand the individual components, like you can with other analytical/window functions. If you want to use the built-in percentiles, you need to regurgitate the incantation. This is certainly one of the less sensible parts of the SQL language. (I don't know if Ordered-Set Aggregate Functions are part of the SQL spec, or if they are a PostgreSQL extension)