SQL · Theory

HAVING

← All stacks

Theory

45/439

HAVING

HAVING sits in the report. Rohit’s job is subjects with avg > 60. Write that first.

Do HAVING once by hand. HAVING AVG(marks) > 60. Change one input. Say the new result out loud.

Without HAVING, the report gets messy and subjects with avg > 60 is hard to trust.

Rohit ships HAVING in the report. That is the use case worth saying.

HAVING trap — WHERE instead of HAVING on aggregates. Rohit loses marks for that every viva.

HAVING is not a lonely heading. Rohit ties it to subjects with avg > 60.

Close HAVING with — “If I skip it, subjects with avg > 60 goes wrong like this: WHERE instead of HAVING on aggregates.”

Exam tip

Say HAVING in one breath, then HAVING AVG(marks) > 60, then WHERE instead of HAVING on aggregates.

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 — Rohit uses it for subjects with avg > 60 in the report.
RuleHAVING → HAVING AVG(marks) > 60.
RememberHAVING + the query planner (the report).
UseHAVING in the report (subjects with avg > 60).
TrapHAVING — WHERE instead of HAVING on aggregates.
ExHAVING → subjects with avg > 60.

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

HAVING — sample query

Short notes

Questions

HAVING

HAVING — sample query

Short notes

Questions