How to use aggregate function in SQL clause that is outside of what you are selecting?-CodePudding

Sorry for the messy title.

I have a database containing different movie titles and each genre that each movie is a part of. I would like to select movies under the genre of "short" that have a movie length that is greater than the average length of movies under all genres.

Genres are denoted as either 1 for yes or 0 for no. This is my select statement so far:

SELECT MovieTitle, MovieLength
FROM MOVIES
WHERE GenreShort = 1
HAVING MovieLength > AVG(MovieLength)

How would I go about comparing movie length to the average of all movies and not just "short" genre movies?

CodePudding user response：

Use an analytic function to only SELECT from the table once:

SELECT MovieTitle,
       MovieLength
FROM   (
  SELECT MovieTitle,
         MovieLength,
         AVG(MovieLength) OVER () AS average_movielength
  FROM   MOVIES
  WHERE  GenreShort = 1
)
WHERE  movielength > average_movielength;

If you want to select from the table twice then:

SELECT MovieTitle,
       MovieLength 
FROM   MOVIES 
WHERE  GenreShort = 1 
AND    MovieLength > (SELECT AVG(MovieLength) FROM MOVIES);

db<>fiddle here

CodePudding user response：

Use a subquery to get the average for all movies and then use that in your comparison, you also need a GROUP BY before using the HAVING clause.

SELECT MovieTitle, MovieLength 
FROM MOVIES 
WHERE GenreShort = 1 
GROUP BY MovieTitle, MovieLength 
HAVING MovieLength > (
                       SELECT AVG(MovieLength)
                       FROM MOVIES
                     );

OR Remove HAVING and GROUP BY

SELECT MovieTitle, MovieLength 
FROM MOVIES 
WHERE GenreShort = 1 AND MovieLength > (
                       SELECT AVG(MovieLength)
                       FROM MOVIES
                     );