In scalar subquery getting error in column alias

Asked Jul 05 '23 at 17:05

Active Jul 05 '23 at 17:05

Viewed 11 times

Select Movie_Title , cast(replace(Box_Office_Revenue_,'$','') as signed) movieCol
From sales.google_movie_data movie
Join (Select round(avg(replace(Box_Office_Revenue_,"$","")),2) as col
from sales.google_movie_data) avg_col
On movie.movieCol > avg_col.col;

Error: Unknown column 'movie.movieCol' in 'on clause'

Why am I getting error here and how to resolve it?

Using MySQL

asked Jul 05 '23 at 17:05

Anubhav Srivastava

Think it will be on `cast(replace(Box_Office_Revenue_,'$','') as signed)` – Nathan_Sav Jul 05 '23 at 17:07
Didn't get you, sorry – Anubhav Srivastava Jul 05 '23 at 17:08
you can't use select field aliases in where or on clauses; you have to repeat the expression instead, so `On cast(replace(Box_Office_Revenue_,'$','') as signed) > avg_col.col` – ysth Jul 05 '23 at 17:10

In scalar subquery getting error in column alias

0 Answers0