Postgres – Multiple conditions on single column with the WHERE AND condition?

So I have a query as follows:

SELECT AVG(ratings.rating)
FROM ratings
INNER JOIN movies 
ON movies.movieid = ratings.movieid
INNER JOIN hasagenre
ON hasagenre.movieid = movies.movieid
INNER JOIN genres
ON hasagenre.genreid = genres.genreid
WHERE genres.name = 'Comedy'

The last line is most important for this, it gets the average rating of all COMEDY movies.

But overall, I have a couple of tables related to a movie database like movies(title,id),genres(name,id),ratings(movie,rating),hasagenre(movie,genre)…..Ex:

Movie     | Genre
------------------
Get Smart | Comedy 
Get Smart | Romance 
Scream    | Horror 

I then want to write a query that gets the average rating of movies that are both COMEDY and ROMANCE, i.e like Get Smart.Thus, I simply updated the last line of query as follows:

SELECT AVG(ratings.rating)
FROM ratings
INNER JOIN movies 
ON movies.movieid = ratings.movieid
INNER JOIN hasagenre
ON hasagenre.movieid = movies.movieid
INNER JOIN genres
ON hasagenre.genreid = genres.genreid
**WHERE (genres.name = 'Comedy' AND genres.name = 'Romance')**

However, that doesn’t work and returns 0 rows even though I know for a fact that there are movies that satisfy both genres in the database. What is going wrong here?

What is the proper syntax for an multiple condition AND clause on the same column?

Answer

If you join all the tables to form an average you will multiply the number of rows due to the multiple genres involved. Instead figure out which movies meet the 2 genre condition first, then average the ratings of just those movies.

SELECT
    AVG(ratings.rating)
FROM ratings
INNER JOIN (
    SELECT
        hasagenre.movieid
    FROM hasagenre
    INNER JOIN genres ON hasagenre.genreid = genres.genreid
    WHERE genres.Name IN ('Comedy', 'Romance')
    GROUP BY
        hasagenre.movieid
    HAVING COUNT(DISTINCT genres.Name) = 2
) m ON ratings.movieid = m.movieid