Msg 8120 Error Help

Transact-SQL
1

Learning SQL, need some help with the following single column median function:

Msg 8120, Level 16, State 1, Line 4
Column 'TBL_US_Counties_Pop_Est_2019.pop_est_2019' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select
sum(pop_est_2019) as county_sum,
round(avg(pop_est_2019),0) as county_average,
percentile_cont(0.5) within group (order by pop_est_2019) over(partition by pop_est_2019) as county_median
From TBL_US_Counties_Pop_Est_2019

2

Hi JB1,

You can not use a field in a group by and this:

percentile_cont(0.5) within group (order by pop_est_2019) over(partition by pop_est_2019) as county_median

You have to use a subquery or use a CTE to get the result you want. If you provide us a ddl of the table structure and some sample data I'm sure we can help you to get the result you want.