SQL · Theory

GROUP BY

Theory

44/439

GROUP BY

GROUP BY city collapses rows that share a city. Then you use aggregates: SELECT city, COUNT(*), AVG(marks) FROM students GROUP BY city;

Every selected column must be in GROUP BY or inside COUNT/SUM/AVG/MIN/MAX (ONLY_FULL_GROUP_BY). SELECT name, AVG(marks) GROUP BY city is illegal — which name?

WHERE filters rows before groups. HAVING filters groups after: HAVING AVG(marks) < 40.

Trap — SELECT * with GROUP BY. Trap: using WHERE AVG(marks) < 40.

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

Exam tip

SELECT city, AVG(marks) FROM students GROUP BY city;

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 buckets rows; aggregates summarise each bucket.
RuleNon-aggregated columns must be in GROUP BY.
TrapWHERE on AVG — use HAVING.

SQL · Theory

GROUP BY

← All stacks

Theory

44/439

GROUP BY

GROUP BY city collapses rows that share a city. Then you use aggregates: SELECT city, COUNT(*), AVG(marks) FROM students GROUP BY city;

Every selected column must be in GROUP BY or inside COUNT/SUM/AVG/MIN/MAX (ONLY_FULL_GROUP_BY). SELECT name, AVG(marks) GROUP BY city is illegal — which name?

WHERE filters rows before groups. HAVING filters groups after: HAVING AVG(marks) < 40.

Trap — SELECT * with GROUP BY. Trap: using WHERE AVG(marks) < 40.

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

Exam tip

SELECT city, AVG(marks) FROM students GROUP BY city;

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 buckets rows; aggregates summarise each bucket.
RuleNon-aggregated columns must be in GROUP BY.
TrapWHERE on AVG — use HAVING.

GROUP BY

GROUP BY — sample query

Short notes

Questions

GROUP BY

GROUP BY — sample query

Short notes

Questions