GROUP BY, HAVING and aggregate semantics

GROUP BY partitions the rows that survived WHERE into groups whose grouping values are equal, with all the NULLs of a column in one group of their own, and turns each group into a single output row. Aggregate functions such as count, sum and avg are computed once per group, and HAVING then keeps or drops whole groups, the way WHERE keeps or drops rows. In relational algebra this is the grouping operator, γ. Because each group becomes one row, the select list may only hold values that are single for a whole group: the grouping columns, aggregates, and in PostgreSQL the columns that depend on a grouped primary key. Two edge cases decide many exam questions: aggregates skip NULLs, and over empty input a query without GROUP BY still returns one row while a query with GROUP BY returns none.

The SQL track’s lessons on GROUP BY and HAVING cover the syntax in each engine. This lesson is about what grouping means, and about the traps that follow from it.

Grouping is a partition

Grouping takes one bag of rows and splits it into disjoint groups: every row lands in exactly one group, the one for its value of the grouping columns. Written as an algebra expression, the query below is

with the grouping columns before the semicolon and the aggregates after it (the query below computes two more, count(marks) and sum(marks)). Eight course results, one of them not marked yet and one from a student whose department is missing:

-- Eight course results. One student has no department on record, and one result is not marked yet.
CREATE TABLE results (roll INTEGER, dept TEXT, course TEXT, marks INTEGER);
INSERT INTO results VALUES
  (101, 'CSE', 'CS201', 78), (102, 'CSE', 'CS201', 64), (103, 'ECE', 'CS201', 55),
  (101, 'CSE', 'MA101', 91), (104, 'ME',  'MA101', NULL), (105, NULL, 'MA101', 47),
  (103, 'ECE', 'EC210', 70), (106, 'ECE', 'EC210', 82);

-- One output row per group. The rows with no department form a group of their own.
SELECT coalesce(dept, 'NULL') AS dept_group, count(*) AS rows_in_group, count(marks) AS marked,
       quote(sum(marks)) AS total, quote(round(avg(marks), 2)) AS average
FROM results
GROUP BY dept
ORDER BY dept;

┌────────────┬───────────────┬────────┬───────┬─────────┐
│ dept_group │ rows_in_group │ marked │ total │ average │
├────────────┼───────────────┼────────┼───────┼─────────┤
│ NULL       │ 1             │ 1      │ 47    │ 47.0    │
│ CSE        │ 3             │ 3      │ 233   │ 77.67   │
│ ECE        │ 3             │ 3      │ 207   │ 69.0    │
│ ME         │ 1             │ 0      │ NULL  │ NULL    │
└────────────┴───────────────┴────────┴───────┴─────────┘

• The NULL department is a group. = would call two NULL departments UNKNOWN, but grouping compares by distinctness, under which NULLs are equal; SQLite’s manual says outright that NULLs are considered equal for grouping (SQLite: SELECT), and PostgreSQL forms the same NULL group.
• count(*) counts rows, count(marks) counts values. ME’s group has one row and no marks, so its counts are 1 and 0, and its sum and avg are NULL: an aggregate of nothing but NULLs is NULL.
• One row per group, whatever the group’s size. The output has 4 rows because there are 4 distinct departments, NULL included, not because of anything in the select list.

WHERE filters rows, HAVING filters groups

WHERE runs before grouping and sees one row at a time; HAVING runs after the aggregates exist and sees one group at a time (PostgreSQL 18: table expressions). The two queries below look alike and answer different questions:

CREATE TABLE results (roll INTEGER, dept TEXT, course TEXT, marks INTEGER);
INSERT INTO results VALUES
  (101, 'CSE', 'CS201', 78), (102, 'CSE', 'CS201', 64), (103, 'ECE', 'CS201', 55),
  (101, 'CSE', 'MA101', 91), (104, 'ME',  'MA101', NULL), (105, NULL, 'MA101', 47),
  (103, 'ECE', 'EC210', 70), (106, 'ECE', 'EC210', 82);

-- WHERE filters rows before grouping: the average of the results of 60 or more, per department.
SELECT dept, count(*) AS results_kept, avg(marks) AS average
FROM results
WHERE marks >= 60
GROUP BY dept
ORDER BY dept;

-- HAVING filters groups after aggregation: the departments whose average is 60 or more.
SELECT dept, count(*) AS results_in_group, avg(marks) AS average
FROM results
GROUP BY dept
HAVING avg(marks) >= 60
ORDER BY dept;

┌──────┬──────────────┬──────────────────┐
│ dept │ results_kept │     average      │
├──────┼──────────────┼──────────────────┤
│ CSE  │ 3            │ 77.6666666666667 │
│ ECE  │ 2            │ 76.0             │
└──────┴──────────────┴──────────────────┘
┌──────┬──────────────────┬──────────────────┐
│ dept │ results_in_group │     average      │
├──────┼──────────────────┼──────────────────┤
│ CSE  │ 3                │ 77.6666666666667 │
│ ECE  │ 3                │ 69.0             │
└──────┴──────────────────┴──────────────────┘

• WHERE marks >= 60 removes the low results first, so ECE’s average is computed over its two good results: 76.0. ME and the NULL group vanish because none of their rows passes.
• HAVING avg(marks) >= 60 keeps every result inside its group and judges the group as a whole: ECE stays with all three results and an average of 69.0. ME’s average is NULL, so its condition is UNKNOWN and HAVING drops it the way WHERE drops an UNKNOWN row.

Where a condition may go depends on what it reads. A condition on an aggregate can only be in HAVING, and one on a column that is not grouped can only be in WHERE. A condition on the grouping columns alone, such as dept <> 'ME', gives the same result in either clause, because it treats every row of a group alike; WHERE is the better home for it, since it removes the rows before any grouping work is done.

What the select list may contain

Each group becomes one row, so each output column needs one value per group. A grouping column has it, and so does an aggregate. A column that is neither has as many values as the group has rows, and SQL must refuse it or pick one.

PostgreSQL refuses it, unless the column is functionally dependent on the grouped columns, and the only dependency it recognises is on a table’s primary key that appears in the GROUP BY list (PostgreSQL 18: SELECT). So after GROUP BY s.roll, where roll is the primary key of students, the query may also select s.name and s.dept, because one roll number fixes them. Selecting a column of another table, such as an enrolment’s course code, still fails with an error saying that the column must appear in the GROUP BY clause or be used in an aggregate function.

SQLite picks a row. It accepts such “bare” columns, and its manual describes what they return (SQLite: SELECT, SQLite: quirks):

CREATE TABLE results (roll INTEGER, dept TEXT, course TEXT, marks INTEGER);
INSERT INTO results VALUES
  (101, 'CSE', 'CS201', 78), (102, 'CSE', 'CS201', 64), (103, 'ECE', 'CS201', 55),
  (101, 'CSE', 'MA101', 91), (104, 'ME',  'MA101', NULL), (105, NULL, 'MA101', 47),
  (103, 'ECE', 'EC210', 70), (106, 'ECE', 'EC210', 82);

-- roll and course are neither grouped nor aggregated. SQLite runs the query anyway: with a single max(),
-- they come from the row that holds the maximum.
SELECT dept, roll, course, max(marks) AS best
FROM results
WHERE dept IS NOT NULL
GROUP BY dept
ORDER BY dept;

-- With avg() there is no such row, so roll comes from some row of the group.
SELECT dept, roll, avg(marks) AS average
FROM results
WHERE dept IS NOT NULL
GROUP BY dept
ORDER BY dept;

┌──────┬──────┬────────┬──────┐
│ dept │ roll │ course │ best │
├──────┼──────┼────────┼──────┤
│ CSE  │ 101  │ MA101  │ 91   │
│ ECE  │ 106  │ EC210  │ 82   │
│ ME   │ 104  │ MA101  │      │
└──────┴──────┴────────┴──────┘
┌──────┬──────┬──────────────────┐
│ dept │ roll │     average      │
├──────┼──────┼──────────────────┤
│ CSE  │ 101  │ 77.6666666666667 │
│ ECE  │ 103  │ 69.0             │
│ ME   │ 104  │                  │
└──────┴──────┴──────────────────┘

• With a single min() or max(), the bare columns come from a row that holds that minimum or maximum: CSE’s best result, 91, belongs to student 101 in MA101, and the query reports exactly that. If several rows tie for the maximum, the manual says the values may come from any of them.
• With any other aggregate, such as avg(), there is no such row, and the bare columns come from an arbitrary row of the group. Here CSE reports roll 101, but nothing in the query asks for that student: the value means nothing, and another version of SQLite could return another row.

The min()/max() rule makes SQLite’s query a convenient answer to “who has the best result in each department”, but the same text is an error in PostgreSQL. The portable form ranks the rows with a window function, or joins each row to its group’s maximum.

Aggregates over NULLs and over nothing

Aggregates ignore NULL inputs, except count(*), which counts rows (PostgreSQL 18: aggregate expressions). The most surprising results come from groups with no rows at all:

CREATE TABLE results (roll INTEGER, dept TEXT, course TEXT, marks INTEGER);
INSERT INTO results VALUES (101, 'CSE', 'CS201', 78), (102, 'CSE', 'CS201', 64);

-- No row passes the WHERE. Without GROUP BY the whole input is one group, so there is one output row.
SELECT count(*) AS n, count(marks) AS marked, quote(sum(marks)) AS total, quote(max(marks)) AS best
FROM results
WHERE course = 'PH110';

-- With GROUP BY there are no groups at all, so the query returns no rows; counting them shows it.
SELECT count(*) AS output_rows
FROM (SELECT dept, count(*) AS n FROM results WHERE course = 'PH110' GROUP BY dept);

-- HAVING without GROUP BY filters that single group: here it is removed.
SELECT count(*) AS output_rows
FROM (SELECT count(*) AS n FROM results HAVING count(*) > 5);

┌───┬────────┬───────┬──────┐
│ n │ marked │ total │ best │
├───┼────────┼───────┼──────┤
│ 0 │ 0      │ NULL  │ NULL │
└───┴────────┴───────┴──────┘
┌─────────────┐
│ output_rows │
├─────────────┤
│ 0           │
└─────────────┘
┌─────────────┐
│ output_rows │
├─────────────┤
│ 0           │
└─────────────┘

• Without GROUP BY, the whole input is one group, even when it is empty. The first query returns one row: count is 0, and sum and max are NULL. SQLite’s manual says such a query always returns exactly one row, and PostgreSQL’s that sum of no rows is NULL, not zero (PostgreSQL 18: aggregate functions).
• With GROUP BY, an empty input has no groups, so the second query returns no rows at all; counting its rows shows 0.
• HAVING without GROUP BY filters that single group, so it can remove it: PostgreSQL’s manual notes that such a query returns one row or none (PostgreSQL 18: table expressions).

Two tools give several counts in a single pass over the rows:

CREATE TABLE results (roll INTEGER, dept TEXT, course TEXT, marks INTEGER);
INSERT INTO results VALUES
  (101, 'CSE', 'CS201', 78), (102, 'CSE', 'CS201', 64), (103, 'ECE', 'CS201', 55),
  (101, 'CSE', 'MA101', 91), (104, 'ME',  'MA101', NULL), (105, NULL, 'MA101', 47),
  (103, 'ECE', 'EC210', 70), (106, 'ECE', 'EC210', 82);

-- Several conditional counts in one pass, with FILTER and with CASE.
SELECT count(*) AS results,
       count(DISTINCT roll) AS students,
       count(DISTINCT course) AS courses,
       count(*) FILTER (WHERE marks >= 60) AS sixty_plus,
       sum(CASE WHEN marks >= 60 THEN 1 ELSE 0 END) AS sixty_plus_case,
       count(*) FILTER (WHERE marks IS NULL) AS not_marked
FROM results;

┌─────────┬──────────┬─────────┬────────────┬─────────────────┬────────────┐
│ results │ students │ courses │ sixty_plus │ sixty_plus_case │ not_marked │
├─────────┼──────────┼─────────┼────────────┼─────────────────┼────────────┤
│ 8       │ 6        │ 3       │ 5          │ 5               │ 1          │
└─────────┴──────────┴─────────┴────────────┴─────────────────┴────────────┘

count(DISTINCT roll) counts the six different students among the eight results; like other aggregates it skips NULLs. count(*) FILTER (WHERE marks >= 60) feeds the aggregate only the rows whose filter condition is TRUE, and sum(CASE WHEN marks >= 60 THEN 1 ELSE 0 END) computes the same 5 in engines without FILTER. PostgreSQL supports FILTER (PostgreSQL 18: aggregate expressions), and SQLite has since version 3.30.0 (SQLite: release history).

Three classic grouped queries

These three questions are common in exams and interviews, and each hides one of the rules above. The first two use a table of faculty salaries in which two people share the top salary:

-- Faculty salaries in thousands; two people share the top salary of 150.
CREATE TABLE faculty (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO faculty VALUES
  (1, 'Anita', 'CSE', 150), (2, 'Bharat', 'CSE', 120), (3, 'Chetan', 'ECE', 150),
  (4, 'Deepa', 'ECE', 95), (5, 'Elango', 'ME', 110), (6, 'Farida', 'ME', 90);

-- Departments whose average salary is above the average of all faculty (715 / 6, about 119.17).
SELECT dept, avg(salary) AS dept_average
FROM faculty
GROUP BY dept
HAVING avg(salary) > (SELECT avg(salary) FROM faculty)
ORDER BY dept;

-- The second-highest salary, without window functions: the largest value below the maximum.
SELECT max(salary) AS second_highest
FROM faculty
WHERE salary < (SELECT max(salary) FROM faculty);

-- The same with a window function: rank the distinct salaries.
SELECT salary AS second_highest
FROM (SELECT DISTINCT salary, dense_rank() OVER (ORDER BY salary DESC) AS r FROM faculty)
WHERE r = 2;

-- A common wrong answer: the second row of the sorted list, which is the tied top salary again.
SELECT salary AS second_row FROM faculty ORDER BY salary DESC LIMIT 1 OFFSET 1;

┌──────┬──────────────┐
│ dept │ dept_average │
├──────┼──────────────┤
│ CSE  │ 135.0        │
│ ECE  │ 122.5        │
└──────┴──────────────┘
┌────────────────┐
│ second_highest │
├────────────────┤
│ 120            │
└────────────────┘
┌────────────────┐
│ second_highest │
├────────────────┤
│ 120            │
└────────────────┘
┌────────────┐
│ second_row │
├────────────┤
│ 150        │
└────────────┘

1. Groups above the overall average. The overall average is a single value, so it goes in a scalar subquery, and the comparison with each group’s average goes in HAVING. CSE (135.0) and ECE (122.5) are above 119.17; ME (100.0) is not.
2. The second-highest salary. “The largest salary below the maximum” gives 120, and so does ranking the distinct salaries with dense_rank(). Taking the second row of the sorted list (LIMIT 1 OFFSET 1) gives 150 again, because two people share the top salary: rows are not values. When every salary is the same, the first two queries correctly return NULL or nothing, while the OFFSET version returns the top salary again.
3. Counts that include zero. A course with no enrolments should show 0, and the usual mistake shows 1:

CREATE TABLE courses (code TEXT PRIMARY KEY, title TEXT);
CREATE TABLE enrolments (roll INTEGER, code TEXT);
INSERT INTO courses VALUES ('CS201', 'Databases'), ('MA101', 'Calculus'), ('PH110', 'Physics');
INSERT INTO enrolments VALUES (101, 'CS201'), (102, 'CS201'), (103, 'MA101');

-- The outer join keeps PH110 with NULLs for the enrolment columns.
SELECT c.code, count(*) AS count_star, count(e.roll) AS count_roll
FROM courses AS c
LEFT JOIN enrolments AS e ON e.code = c.code
GROUP BY c.code
ORDER BY c.code;

┌───────┬────────────┬────────────┐
│ code  │ count_star │ count_roll │
├───────┼────────────┼────────────┤
│ CS201 │ 2          │ 2          │
│ MA101 │ 1          │ 1          │
│ PH110 │ 1          │ 0          │
└───────┴────────────┴────────────┘

The LEFT JOIN keeps PH110 by pairing it with one row of NULLs, so its group has one row. count(*) counts that row and reports 1; count(e.roll) skips the NULL roll and reports 0. Count a column of the outer-joined table, or better its key, whenever empty groups must show zero.

Interview questions

Fresher · What is the difference between WHERE and HAVING, and when can a condition go in either?

WHERE filters rows before grouping, so it cannot use an aggregate; HAVING filters groups after the aggregates are computed, so it can. WHERE marks >= 60 changes which rows are averaged, while HAVING avg(marks) >= 60 keeps or drops whole groups by their average. A condition on the grouping columns only, such as dept <> 'ME', gives the same result in either clause, because it treats every row of a group alike, and WHERE is the cheaper place for it. A condition on an ungrouped column can only go in WHERE. Both clauses keep only TRUE, so a group whose average is NULL fails HAVING avg(marks) >= 60.

Key takeaways

• GROUP BY partitions the rows into groups of equal grouping values, NULLs together in one group, and returns one row per group.
• WHERE filters rows before grouping; HAVING filters groups after it. A condition on an aggregate belongs in HAVING, one on the grouping columns is best in WHERE.
• The select list may hold grouping columns, aggregates and, in PostgreSQL, columns that depend on a grouped primary key. SQLite also accepts bare columns: from the min or max row with a single min() or max(), otherwise from an arbitrary row.
• Aggregates skip NULLs; count(*) counts rows; over empty input, a query without GROUP BY returns one row (count 0, sum NULL) and a query with GROUP BY returns none.
• count(*) after a LEFT JOIN counts the NULL-extended row: count the joined table’s key to get zero.
• The second-highest value is a question about values, not rows: use max below the max, or dense_rank().