Getting an error for "unknown column" in my SQL code in MySQL that I don't understand, because the column alias exists

So, I'm FRESH fresh to SQL, and frankly, I hate it, but I have to pass this class. I've only been learning it for a couple of months through a course and I'm running into an issue. For some context, I'm working on a project for a class, and I'm trying to list "overrated" actors from my movie database. I've decided the criteria for an overrated actor is one with more than eight films with an IMDB rating under 8.0.

My problem is I keep getting "Error Code: 1054: Unknown Column 'Average IMDB Rating' in 'Where Clause.' I'm not seeing where my problem is (see the code), as I clearly define the column alias in my code. I'm supposed to stick to "coding standards," which means apparently table alias's have to be backticks. Fine, but every time I use backticks, I get this error. I've checked MySQL's documentation and didn't find it helpful, and my web searches didn't get me answers that matched my issue in a way I can use (we can only use code we've learned, so think basic solutions. We've learned up through cases and basic aggregation by this point.)

Lastly, I can't go to the instructor, as he'll dock points for helping me with specific query problems. So here I am, coming to the one place I think is my last shot for understanding and fixing this error.

I'm using MySQL for this, btw.

The code:

SELECT 
    CONCAT(FirstName, ' ', LastName) AS `Actors and Actresses`,
    COUNT(ma.CelebrityId) AS `Number of Movies`,
    ROUND(AVG(IMDBRating), 2) AS `Average IMDB Rating`, 
    min(IMDBRating) as `Mininum Rating`,
    SUM(NumVotes) AS `Total Votes`
FROM
    movie
        INNER JOIN
    movieactor AS ma ON movie.MovieId = ma.MovieId
        INNER JOIN
    celebrity ON ma.CelebrityId = celebrity.CelebrityId
WHERE
    `Average IMDB Rating` < 8.0
        AND `Number of Movies` > 8
GROUP BY FirstName, LastName
ORDER BY `Average IMDB Rating` asc;

Solution

There are a few things to note here

- you cannot use aggregated columns directly on WHERE clause, if aggregated columns needs to be filtered you can use HAVING alongside groupBy.

- (as seen in your first version of the question, that you fixed in your second version):
Column aliases cannot be used with space like Average IMDB Rating, if you really want to use space you need to wrap with quotes like `Average IMDB Rating` (MySQL default quoting) or "Average IMDB Rating" (more widespread, but requires the ANSI_QUOTES option for MySQL), otherwise simply use underscores as in Average_IMDB_Rating.

So your corrected sample query becomes:

SELECT 
    CONCAT(FirstName, ' ', LastName) AS Actors_and_Actresses,
    COUNT(ma.CelebrityId) AS Number_of_Movies,
    ROUND(AVG(IMDBRating), 2) AS Average_IMDB_Rating, 
    min(IMDBRating) as Mininum_Rating,
    SUM(NumVotes) AS Total_Votes
FROM
    movie
        INNER JOIN
    movieactor AS ma ON movie.MovieId = ma.MovieId
        INNER JOIN
    celebrity ON ma.CelebrityId = celebrity.CelebrityId
GROUP BY 
    FirstName, LastName
HAVING 
  Average_IMDB_Rating  < 8.0
  AND Number_of_Movies > 8
ORDER BY Average_IMDB_Rating asc;

Similar approach using quotes in column alias:

SELECT 
    CONCAT(FirstName, ' ', LastName) AS `Actors and Actresses`,
    COUNT(ma.CelebrityId) AS `Number of Movies`,
    ROUND(AVG(IMDBRating), 2) AS `Average IMDB Rating`, 
    min(IMDBRating) as `Mininum Rating`,
    SUM(NumVotes) AS `Total Votes`
FROM
    movie
        INNER JOIN
    movieactor AS ma ON movie.MovieId = ma.MovieId
        INNER JOIN
    celebrity ON ma.CelebrityId = celebrity.CelebrityId

GROUP BY FirstName, LastName
HAVING `Average IMDB Rating` < 8.0
 AND `Number of Movies` > 8
ORDER BY `Average IMDB Rating` asc;