GROUP BY and HAVING
Grouping rows and computing an aggregate per group with GROUP BY, filtering groups with HAVING rather than WHERE, and knowing the order in which the database evaluates the clauses.
- 18 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
A few cards from the deck
GROUP BY: what is it?
Show the answer
The clause that sorts rows into groups of identical values, so that an aggregate is computed once per group.
The result has one row per group. In SELECT, only the grouping columns and aggregates may appear.
Which rows does this query return?
```sql
-- client : Durand Lyon | Martin Paris | Bassam Lyon | Dao Lille
-- Leroy Paris | Rossi Milan | Conti Milan | Weber Berlin | Keller Lyon
SELECT ville, COUNT(*)
FROM client
GROUP BY ville
HAVING COUNT(*) >= 2;`- Lyon 3, Milan 2, Paris 2
- Lyon 3
- Berlin 1, Lille 1, Lyon 3, Milan 2, Paris 2
- Lyon 3, Paris 2
Show the answer
Lyon 3, Milan 2, Paris 2
The groups are Berlin 1, Lille 1, Lyon 3, Milan 2, Paris 2. HAVING drops Berlin and Lille, which have a single client. The threshold is “at least 2”, so Milan and Paris stay.
To get one row per city with the number of clients in each, end
SELECT ville, COUNT(*) FROM clientwith ….Show the answer
To get one row per city with the number of clients in each, end
SELECT ville, COUNT(*) FROM clientwith GROUP BY ville.Each city forms a group and COUNT(*) counts the rows of that group. Without GROUP BY, the query fails: ville is not aggregated.
HAVING: what is it?
Show the answer
The clause that filters groups after grouping, usually on the value of an aggregate.
HAVING COUNT(*) >= 2keeps groups of at least two rows. A dropped group disappears entirely from the result.