MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table nm_sales_population(id int, city varchar(128), total_sales int);
insert into nm_sales_population values (1, 'NYC', 100);
insert into nm_sales_population values (2, 'NYC', 200);
insert into nm_sales_population values (3, 'LA', 100);
select 
    *,
case
    when total_sales > "City Average Total Sales" then "Above Average"
    when total_sales < "City Average Total Sales" then "Below Average"
    when total_sales = "City Average Total Sales" then "Equal to Average"
end as "Sales Tracking"
from (
    select 
        *,
        round(avg(total_sales) over (Partition by city), 2) as "City Average Total Sales"
    from nm_sales_population) a;


id
city
total_sales
City Average Total Sales
Sales Tracking


3
LA
100
100.00
Above Average

1
NYC
100
150.00
Above Average

2
NYC
200
150.00
Above Average


select 
    *,
case
    when total_sales > `City Average Total Sales` then "Above Average"
    when total_sales < `City Average Total Sales` then "Below Average"
    when total_sales = `City Average Total Sales` then "Equal to Average"
end as "Sales Tracking"
from (
    select 
        *,
        round(avg(total_sales) over (Partition by city), 2) as "City Average Total Sales"
    from nm_sales_population) a;


id
city
total_sales
City Average Total Sales
Sales Tracking


3
LA
100
100.00
Equal to Average

1
NYC
100
150.00
Below Average

2
NYC
200
150.00
Above Average

id	city	total_sales	City Average Total Sales	Sales Tracking
3	LA	100	100.00	Above Average
1	NYC	100	150.00	Above Average
2	NYC	200	150.00	Above Average

id	city	total_sales	City Average Total Sales	Sales Tracking
3	LA	100	100.00	Equal to Average
1	NYC	100	150.00	Below Average
2	NYC	200	150.00	Above Average