SQL GROUP BY errors: "misuse of aggregate" and "must appear in the GROUP BY clause"

GROUP BY is where SQL starts to feel powerful: one short query, and you know how many courses each student takes or the average score in each class. It's also where most people meet their first truly confusing SQL errors, and one mistake that gives no error at all, just a wrong answer.

All examples use this small table of course enrollments, and we ran every query in SQLite (the database our SQL course uses) and in PostgreSQL:

CREATE TABLE enrollments (student TEXT, course TEXT, score INTEGER);

INSERT INTO enrollments VALUES
  ('Alex', 'Python', 72), ('Alex', 'SQL', 85),
  ('Sam', 'Python', 90), ('Sam', 'Web', 64),
  ('Mia', 'SQL', 88), ('Mia', 'Web', 79), ('Mia', 'Python', 81),
  ('Leo', 'Python', 58);

What GROUP BY does

COUNT, AVG, SUM, MIN and MAX are aggregate functions: they turn many rows into one value. On their own, they work on the whole table:

SELECT COUNT(*) FROM enrollments;

COUNT(*)
--------
8

GROUP BY first sorts the rows into groups with the same value, then runs the aggregate once per group:

SELECT student, COUNT(*) AS courses
FROM enrollments
GROUP BY student;

student  courses
-------  -------
Alex     2
Leo      1
Mia      3
Sam      2

Keep that picture in mind: after GROUP BY student, each result row stands for a group of rows, not a single row. Both errors below come from forgetting that.

The silent one: a column without GROUP BY

Leave out the GROUP BY but keep the student column:

SELECT student, COUNT(*) FROM enrollments;

In SQLite, this runs without any error:

student  COUNT(*)
-------  --------
Alex     8

It looks like Alex takes eight courses. He takes two. The 8 is the number of rows in the whole table, and "Alex" is simply the name from one of those rows. SQLite allows a plain column next to an aggregate and fills it from one of the rows in the group, and which one is not something you can rely on.

PostgreSQL, and most other databases, refuse:

ERROR: column "enrollments.student" must appear in the GROUP BY clause or be used in an aggregate function

That message is the rule, spelled out. Every column in the SELECT must either be in the GROUP BY (so it has one value per group) or be inside an aggregate (so many values become one). A plain student without GROUP BY is neither, so it has no single correct value.

The fix: decide what you meant. If you wanted the count per student, add GROUP BY student. If you wanted the total, remove the student column.

The same mistake with an extra column

It also happens when you group by one column but select another:

SELECT student, course, AVG(score) AS average
FROM enrollments
GROUP BY student;

student  course  average
-------  ------  -------
Alex     Python  78.5
Leo      Python  58
Mia      SQL     82.66666666666667
Sam      Python  77

The averages are right, but the course column is meaningless: Alex takes Python and SQL, and his group has one row in the result, so SQLite shows one of the two. PostgreSQL stops with the same error as before, this time about enrollments.course.

Either remove course, or group by both columns (GROUP BY student, course), which gives one row per student and course, or summarise it with an aggregate, like COUNT(course).

misuse of aggregate: COUNT()

Now you want only the students who take two or more courses. The natural first try is WHERE:

SELECT student, COUNT(*) AS courses
FROM enrollments
WHERE COUNT(*) >= 2
GROUP BY student;

misuse of aggregate: COUNT()

ERROR: aggregate functions are not allowed in WHERE

The PostgreSQL message explains it best. WHERE looks at one row at a time, before the groups exist. A single row doesn't have a count yet, so COUNT(*) can't be used there.

The fix: use HAVING, which filters the groups after GROUP BY has made them:

SELECT student, COUNT(*) AS courses
FROM enrollments
GROUP BY student
HAVING COUNT(*) >= 2;

student  courses
-------  -------
Alex     2
Mia      3
Sam      2

Leo, with one course, is gone.

WHERE and HAVING together

They aren't rivals; they work at different moments, and you can use both in one query:

1. WHERE keeps or drops rows.
2. GROUP BY makes groups from the rows that are left.
3. HAVING keeps or drops groups.

For example, the average score of each student without their web courses, keeping only students whose average is above 75:

SELECT student, AVG(score) AS average
FROM enrollments
WHERE course <> 'Web'
GROUP BY student
HAVING AVG(score) > 75;

student  average
-------  -------
Alex     78.5
Mia      84.5
Sam      90

Sam's 64 in Web is dropped by WHERE before his group is made, so his average is 90. Leo's group exists, but its average of 58 is dropped by HAVING.

A rule of thumb: if the condition is about a single row (a course, a score, a date), it goes in WHERE. If it's about a group (a count, an average, a total), it goes in HAVING.

One more difference: names in HAVING

SQLite lets you use the name you gave a column with AS in HAVING:

SELECT student, COUNT(*) AS courses
FROM enrollments
GROUP BY student
HAVING courses >= 2;

That works in SQLite, but PostgreSQL answers ERROR: column "courses" does not exist, because the name only exists in the result. Writing the aggregate again, HAVING COUNT(*) >= 2, works everywhere. (ORDER BY courses works in both.)

A quick checklist

1. For every column in your SELECT, ask: is it in the GROUP BY, or inside an aggregate? If neither, the result for it is meaningless, even if SQLite doesn't complain.
2. A condition on a count, average or total goes in HAVING, not WHERE.
3. A condition on single rows goes in WHERE, which can also make the query faster, because fewer rows get grouped.
4. If two students could have the same name, group by a unique id instead of the name, so they aren't merged into one.
5. If a number looks too big or too round, check whether you forgot the GROUP BY and counted the whole table.

The error most people meet even before GROUP BY is a word without quotes: see SQL error "no such column" in SQLite. And if you're wondering whether SQL is a good first language, our guide Which programming language should you learn first? compares it with Python, JavaScript and Java.