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.
2
Answers
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:
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).
Your query is largely accurate – all you need to do is use a backquote instead of the double-quotes for the column
City Average Total Sales
The double-quotes are causing "City Average Total Sales" to be interpreted as a literal instead of a column name.