Comparison operators with a CASE statement and Window Function

I’m trying to use a case function with a window function, but the data I’m getting doesn’t add up. Everything seems to work, but I’m only receiving "Above Average" for that entire CASE function, so I know something isn’t quite right. I would love for each row to give an indication of whether those sales were above, below, or equal to that city’s average.

I feel like it’s a simple syntax thing, but I can’t figure out where I’m going wrong. Please help!

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(total_sales / Population, 0) AS "Revenue Over Residents",
        round(avg(total_sales) over (Partition by city), 2) as "City Average Total Sales"
    from nm_cannabis_sales_population) a;

Again, I’m getting the Above Average result for that entire column, and I’m using MySQL if that helps.

>Solution :

The problem is that you are comparing a value (say "100") to the string "City Average Total Sales" (as opposed to the column named "City Average Total Sales".)

To fix the ambiguity, you can use backticks for the column name, like:

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"

You can see the problem and solution in this Fiddle.

An alternate solution is to set your sql mode to ANSI_QUOTES. That way MySQL will treat the double quotes in your outer query the way that I think you are expecting (and the way most other DBMSes do).