WHERE子句使用COUNT()引发GROUP BY错误（错误码1111）

Hey there! Let's get to the bottom of that Error Code 1111 and adjust your query to only pull in subjects who've watched more than one movie.

Why You're Getting the Error #

The Invalid use of group function error happens because you're trying to use the COUNT() aggregate function in a WHERE clause. Here's the key difference to remember:

• WHERE filters individual rows before any grouping happens. It doesn't have access to aggregated values like counts from groups.
• HAVING is made for filtering groups of rows after aggregation is done. That's where you want to put your COUNT() condition.

The Solution: Filter Valid Subjects First #

We need to first identify which subjects have watched more than one movie, then fetch all their related records from the measures table. Here are two solid approaches:

Option 1: Use a Subquery with IN

SELECT m.Subject_id, m.Movie_id, m.Median_heart_rate AS Variation
FROM measures m
WHERE m.Subject_id IN (
    -- First get all subjects with >1 movie
    SELECT Subject_id
    FROM measures
    GROUP BY Subject_id
    HAVING COUNT(Movie_id) > 1
);

Option 2: Use a JOIN with a Filtered Subquery

This is often more efficient for larger datasets:

SELECT m.Subject_id, m.Movie_id, m.Median_heart_rate AS Variation
FROM measures m
JOIN (
    -- Subquery to get valid subjects
    SELECT Subject_id
    FROM measures
    GROUP BY Subject_id
    HAVING COUNT(Movie_id) > 1
) valid_subjects ON m.Subject_id = valid_subjects.Subject_id;

How This Works #

• The inner subquery groups rows by Subject_id, then uses HAVING COUNT(Movie_id) > 1 to only keep subjects who have multiple entries (since Subject_id + Movie_id is a composite primary key, each entry represents a unique movie watched).
• The main query then pulls all records from measures that belong to these valid subjects, so you'll only see data for subjects like 001 and 003, not 002.

Quick Note #

Since Subject_id and Movie_id form a composite primary key, COUNT(Movie_id) will work exactly like COUNT(*) here—no null values to worry about. Using COUNT(Movie_id) just makes your intent a bit clearer!

内容的提问来源于stack exchange，提问作者Cornelis