Learn SQL  /  Aggregation  /  HAVING

HAVING

HAVING filters GROUPS after aggregation, the same way WHERE filters individual ROWS before aggregation. This is one of the most confused concepts for SQL beginners.

Why WHERE Cannot Filter on an Aggregate

R Code
-- this FAILS: an aggregate like COUNT(*) cannot go in WHERE
SELECT class, COUNT(*) AS n
FROM students
WHERE COUNT(*) > 1   -- ERROR
GROUP BY class;
Output
ERROR: aggregate functions are not allowed in WHERE

This fails because WHERE runs before grouping/aggregation even happens - at that point, the database has not computed COUNT(*) yet. HAVING exists specifically to solve this.

Basic HAVING

R Code
SELECT class, COUNT(*) AS n
FROM students
GROUP BY class
HAVING COUNT(*) > 1;
Output
class | n
A     | 2
B     | 2

HAVING with a Different Aggregate

R Code
SELECT class, AVG(score) AS avg_score
FROM students
GROUP BY class
HAVING AVG(score) > 80;
Output
class | avg_score
A     | 90.0

Using WHERE and HAVING Together

🚀 Advanced Example
WHERE and HAVING can appear in the same query, doing two different jobs - WHERE trims rows first, then HAVING trims the resulting groups.
R Code
SELECT class, COUNT(*) AS n, AVG(score) AS avg_score
FROM students
WHERE age < 25          -- step 1: filter rows
GROUP BY class              -- step 2: group what is left
HAVING COUNT(*) >= 2;  -- step 3: keep only groups meeting this
Output
class | n | avg_score
B     | 2 | 70.5

Key Points

  • WHERE filters individual rows BEFORE grouping; HAVING filters entire groups AFTER aggregation.
  • Aggregate functions (COUNT, AVG, SUM...) can be used in HAVING conditions but never in WHERE.
  • The logical order is: WHERE, then GROUP BY, then HAVING, then ORDER BY.
  • WHERE and HAVING can both appear in the same query, each doing its own job.