SQL Lab: groups¶

Exercise sql-lab-groups-annex.pdf · PDF

How many fire Pokémon are there? You know how to find out: one query with WHERE type_1 = 'fire', and the answer is 59. Now do water. Then grass. Then the other fifteen types.

Eighteen queries for one small question. Nobody wants that. And the questions people really ask about data are almost never "show me the cards". They are how many, how much, what is the average, which group wins. In the last lesson every answer was a list of cards. In this one, the answer is a number about many cards — and then the same number for every group at once.

One number for the whole box¶

An aggregate function reads many rows and returns one value.

We keep the shoebox from SQL Lab: filters: the table pokemon is a box of 949 trading cards, a row is a card, a column is a line printed on it. An aggregate function reads a whole stack of cards and says one number.

Open SQL → SQL Lab (database Dades classe, schema public) and run:

SELECT MIN(attack), MAX(attack), SUM(attack), AVG(attack) FROM pokemon;

949 cards went in. One row came out. That is the mark of an aggregate: however big the table, you get a single line.

The average is correct and unreadable. Two small tools fix that:

SELECT ROUND(AVG(attack), 1) AS avg_attack FROM pokemon;

• ROUND(value, 1) rounds to one decimal. ROUND(value) rounds to a whole number.
• AS gives the result column a name you choose. That name is called an alias.

An aggregate works together with WHERE just as you would expect: first the cards are taken out, then they are counted. SELECT COUNT(*) FROM pokemon WHERE type_1 = 'fire' returns 59.

COUNT(*) is not COUNT(column)¶

Same table, three counts:

SELECT COUNT(*) AS total,
       COUNT(type_2) AS with_second_type,
       COUNT(DISTINCT type_1) AS types
FROM pokemon;

949 and 510, from the same box. Who is lying? Nobody. They answer different questions:

And 949 − 510 = 439: the single-type Pokémon you found with IS NULL in the last lesson.

GROUP BY: make piles¶

GROUP BY splits the rows into groups, one group per different value of a column, and returns one result row per group.

Take a deck of playing cards and deal it into piles, one pile per suit. How many piles? One per different suit. Now count each pile. How many numbers do you get? One per pile.

That is all GROUP BY does: one pile per different value, one result row per pile.

SELECT type_1, COUNT(*) AS n
FROM pokemon
GROUP BY type_1
ORDER BY n DESC;

18 rows. The first three and the last one:

The eighteen queries from the top of the page, in four lines. Remember SELECT DISTINCT type_1? It also returned 18 rows. Same piles; now each pile also gets counted.

You can ask several figures of each pile at once:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
GROUP BY type_1
ORDER BY avg_attack DESC;

flowchart LR
    A["<b>The box</b><br>949 cards"] --> B["<b>GROUP BY type_1</b><br>deal them into piles"]
    B --> P1["water<br>126 cards"]
    B --> P2["normal<br>111 cards"]
    B --> P3["...16 more piles"]
    P1 --> R["<b>Result</b><br>18 rows:<br>one per pile"]
    P2 --> R
    P3 --> R

The error everybody meets¶

Add the name of the Pokémon to the query above:

SELECT type_1, pokemon, COUNT(*) FROM pokemon GROUP BY type_1;

ERROR:  column "pokemon.pokemon" must appear in the GROUP BY clause
        or be used in an aggregate function

Read it with the piles in front of you. The water pile has 126 cards. Which one name should the database print for the whole pile? It cannot choose, so it refuses.

The rule: in a grouped query, every column in SELECT is either what you made the piles by, or inside an aggregate function.

HAVING: throw away whole piles¶

HAVING keeps only the groups for which a condition is true. It is a filter for piles, not for cards.

Look again at the last row of the first result: flying, 4 cards. An average of four cards is not worth much. Keep only the big piles:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
GROUP BY type_1
HAVING COUNT(*) >= 50
ORDER BY avg_attack DESC;

Eight piles are left. dragon, the winner a moment ago, is gone: it only has 39 cards.

WHERE or HAVING? Cards or piles¶

This is the idea people mix up most. Both lines filter. They filter different things, at different moments.

That is why this fails:

SELECT type_1, COUNT(*) FROM pokemon WHERE COUNT(*) >= 50 GROUP BY type_1;

ERROR:  aggregate functions are not allowed in WHERE

When WHERE runs, there are no piles yet. There is nothing to count.

A query can use both. Average attack per type, only types with at least 50 Pokémon, ignoring the Mega evolutions:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
WHERE pokemon NOT LIKE '%-mega%'
GROUP BY type_1
HAVING COUNT(*) >= 50
ORDER BY avg_attack DESC;

"Is not a Mega" is a question about each card: WHERE. "Has at least 50" is a question about each pile: HAVING. Compare with the previous table: rock dropped from 90 to 87 and fire from 84 to 81. Mega evolutions are very strong, so they were pulling the averages up.

The real order of a query¶

You now know seven lines. You write them in one order. The database executes them in another — and that order explains every rule on this page.

flowchart LR
    F["<b>1 · FROM</b><br>open the box"] --> W["<b>2 · WHERE</b><br>take cards out"]
    W --> G["<b>3 · GROUP BY</b><br>make piles"]
    G --> H["<b>4 · HAVING</b><br>drop whole piles"]
    H --> S["<b>5 · SELECT</b><br>read and compute"]
    S --> O["<b>6 · ORDER BY</b><br>sort"]
    O --> L["<b>7 · LIMIT</b><br>keep the top"]

WHERE runs before the piles exist, so it cannot count them. HAVING runs before SELECT, so it does not know your aliases. ORDER BY runs after SELECT, so it does.

Try it: which type wins?¶

One query per question, in SQL Lab. Write yours first, then compare.

Question 4 leaves two loose ends. Who are the 147 cards with no generation? And what is generation 5 — which region, which year? The table has a number, but not what it means.

Key takeaways¶

• An aggregate function (COUNT, SUM, AVG, MIN, MAX) turns many rows into one value. ROUND tidies it, AS names it with an alias.
• COUNT(*) counts rows; COUNT(column) counts the rows where that column is not blank. Aggregates skip NULL.
• GROUP BY makes one pile per different value and returns one row per pile. It does not sort, and blanks form their own pile.
• In a grouped query, every column in SELECT is a pile key or sits inside an aggregate.
• WHERE filters cards before the piles are made; HAVING filters piles after they are counted.
• The database executes FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Quick check¶

Generation 5 has 156 Pokémon — but which region is generation 5? That is written in another table. Next: SQL Lab: joins, where you read two tables as one, and the 147 cards cause trouble.