SQL · Theory

HAVING

Theory

45/439

HAVING

HAVING is WHERE for groups. SELECT city, AVG(marks) FROM students GROUP BY city HAVING AVG(marks) < 50; cities whose average is below 50.

You cannot put AVG(marks) in WHERE because WHERE runs before groups exist. Remember: WHERE rows → GROUP BY → HAVING groups → ORDER BY.

Trap: HAVING without GROUP BY on a detail query just to look clever. Trap: repeating the same AND in WHERE and HAVING without knowing which stage it is.

On the example next to this theory — HAVING — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Exam tip

GROUP BY city HAVING AVG(marks) < 50;

HAVING — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

HAVING — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefHAVING filters groups after GROUP BY.
RuleAggregates belong in HAVING, not WHERE.
TrapWHERE AVG(marks) < 50.

SQL · Theory

HAVING

← All stacks

Theory

45/439

HAVING

HAVING is WHERE for groups. SELECT city, AVG(marks) FROM students GROUP BY city HAVING AVG(marks) < 50; cities whose average is below 50.

You cannot put AVG(marks) in WHERE because WHERE runs before groups exist. Remember: WHERE rows → GROUP BY → HAVING groups → ORDER BY.

Trap: HAVING without GROUP BY on a detail query just to look clever. Trap: repeating the same AND in WHERE and HAVING without knowing which stage it is.

On the example next to this theory — HAVING — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Exam tip

GROUP BY city HAVING AVG(marks) < 50;

HAVING — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

HAVING — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefHAVING filters groups after GROUP BY.
RuleAggregates belong in HAVING, not WHERE.
TrapWHERE AVG(marks) < 50.

HAVING

HAVING — sample query

Short notes

Questions

HAVING

HAVING — sample query

Short notes

Questions