COUNT, SUM, AVG and GROUP BY: collapsing rows honestly
Turn many rows into a few numbers, group them by item — and see how an average can hide exactly what you need to know.
Filtering narrows a table down, but some questions need the opposite move: collapsing many rows into one number. How much did the canteen earn today? How many orders were there? You cannot answer those by showing rows — you need aggregation functions, and you have already met the idea: this is aggregation over a set, the same move you did in the logic module, now on a real table.
SELECT COUNT(*) FROM sales
4 rows in, 1 row out
SELECT SUM(count) FROM sales
40 + 55 + 23 + 60 = 178One row comes back, no matter how many rows went in.The four functions you will use constantly: COUNT counts rows (or values), SUM adds them up, AVG gives the mean, MAX and MIN return the largest and smallest. Each one eats many values and returns exactly one. COUNT(*) counts rows as they are; COUNT(paid) counts only rows where paid is not NULL — the difference is often exactly the story.
The canteen wants one number per dish: how many units of it sold in total. Without grouping you would have to write four separate queries with WHERE item = 'soup', WHERE item = 'juice' and so on. GROUP BY does all of them in one query and guarantees every dish gets exactly one row.
SELECT item, SUM(count)
FROM sales
GROUP BY item
+---------+------------+
| item | SUM(count) |
+---------+------------+
| soup | 40 |
| juice | 75 |
| salad | 23 |
| sandwich| 55 |
+---------+------------+One row per item: each group collapses into its total.Here is the mechanics. The four rows fall into four groups by their item value. Inside each group, SUM(count) adds up the counts. The result has one row per group — not per original row. Which columns may stand next to the aggregate in SELECT follows from this: only ones that have a single value inside the group.
SELECT item, price, SUM(count)
FROM sales
GROUP BY item
item -> one value per group
SUM(count) -> one value per group
price -> many values per group, no single answerWhy mixing grouped and non-grouped columns has no answer.Now the subtlest trap, and it needs no wrong SQL at all. The canteen measures the queue in minutes. Two days, and the averages are almost the same. Does the mean of 3.2 and the mean of 3.4 tell you these days were alike?
queue times in minutes for two days
monday: 1, 1, 1, 1, 12 AVG = 3.2
tuesday: 2, 3, 3, 4, 5 AVG = 3.4
same average, different daysThe same average built from two very different days.Monday was calm: four pupils waited a minute, one unlucky one waited twelve. Tuesday was a steady, mild wait for everyone. The averages agree, the days have nothing in common. An average is one number doing the work of a whole distribution, and by itself it cannot say whether the twelves are rare or normal.
Practice
Write one query that shows each item with the total number of units sold. The sales table has columns item, price and count.
Hints
- The answer must be one row per item, and GROUP BY is what makes one row per group.
- Inside each group you need the total of count, which is SUM(count).
- The order is: what you keep and how you collapse it in SELECT, the table in FROM, the grouping in GROUP BY.
A classmate writes: SELECT item, price, SUM(count) FROM sales GROUP BY item. Why does the database refuse this query?
Hints
- For every column in the SELECT ask: does this column have exactly one value inside a group?
- item is the group itself, price is a different number on different rows of the same group.
The canteen sees that the average queue yesterday was 10 minutes. What is the most useful thing to check before drawing a conclusion?
Hints
- An average of 10 can be built from 10 and 10, or from 1 and 19 — which queue would you rather stand in?
- The canteen does not need the exact list to act: it needs to know whether the long waits are rare or normal.