SQL · Theory

GROUP BY

← All stacks

Theory

44/439

GROUP BY

Amit only reaches for GROUP BY when average per subject has to stay correct in a report.

GROUP BY order — meaning → GROUP BY subject → one limit. Dumping ten features helps nobody.

Without GROUP BY, a report gets messy and average per subject is hard to trust.

Amit ships GROUP BY in a report. That is the use case worth saying.

GROUP BY trap — SELECT extra column not in GROUP BY. Amit loses marks for that every viva.

After GROUP BY, Amit should still remember SELECT extra column not in GROUP BY.

Close GROUP BY with — “If I skip it, average per subject goes wrong like this: SELECT extra column not in GROUP BY.”

Exam tip

Say GROUP BY in one breath, then GROUP BY subject, then SELECT extra column not in GROUP BY.

GROUP BY — 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;

GROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefGROUP BY — Amit uses it for average per subject in a report.
RuleGROUP BY → GROUP BY subject.
RememberGROUP BY + InnoDB (a report).
UseGROUP BY in a report (average per subject).
TrapGROUP BY — SELECT extra column not in GROUP BY.
ExGROUP BY → average per subject.

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;

GROUP BY

GROUP BY — sample query

Short notes

Questions

GROUP BY

GROUP BY — sample query

Short notes

Questions