Counting and Summing: GROUP BY

"How many bookings does each screening have?" Look closely and this is a different kind of question from everything so far. Every answer in this chapter has been made of rows from a table — this film, that customer. But the rows of this answer aren't bookings; they're summaries of bookings, one row per screening, with a count attached.

Two tools make that possible. An aggregate function — COUNT, SUM, AVG, MIN, MAX — collapses many rows into one number. And GROUP BY decides what "many" means: without it, the aggregate swallows the whole table; with it, the table is dealt into groups and the aggregate runs once per group. This page is how every dashboard number in the world actually gets made.

Collapsing a Table to One Number

Start with the simplest aggregate question: how many bookings are there, in total? The function COUNT(*) counts rows, and with no grouping in sight it counts all of them:

SELECT COUNT(*) FROM bookings;

Fifty-four bookings, one row, one number. Notice how strange that answer is by this chapter's standards: no booking appears in it. The whole table collapsed into a single summary row. The other aggregates behave the same way — SUM(price) adds a column up, AVG(price) averages it, MIN(joined_on) and MAX(joined_on) find the earliest and latest — each one eats many rows and hands back one value.

GROUP BY Changes the Unit

Lora's real question wasn't the grand total, though — it was per screening. GROUP BY is how you say "per": deal the rows into one pile for each distinct value of a column, then run the aggregate once per pile. Sorting the till receipts is exactly this — one pile per film night, then count each pile. The piles are GROUP BY; the counting is the aggregate. Keep the picture for a page, because we drew it from real mechanics:

SELECT screening_id, COUNT(*) AS bookings_count
FROM bookings
GROUP BY screening_id;

Walk the mechanics slowly with a small excerpt of the bookings table. Eight bookings, spread over three screenings:

GROUP BY screening_id deals these eight rows into three piles — the screening-1 pile holds four rows, the screening-2 pile holds one, the screening-3 pile holds three. Then COUNT(*) runs once per pile, and each pile becomes exactly one output row:

SUM works the same way with money. Group the screenings table by film and add up the ticket prices, and you get each film's prices totaled across its showings — call it the one-seat revenue, what a film would earn if exactly one ticket sold per screening. Real revenue would need each booking matched to its screening's price, which takes two tables at once; and turning film_id into a title needs the films table too. Both honestly need Chapter 4 — one page away, and now you know why it exists:

SELECT film_id, SUM(price) AS one_seat_revenue
FROM screenings
GROUP BY film_id;

The Iron Rule

Now the rule that every beginner meets as an error message before meeting it as an idea: in a grouped query, every column you SELECT must either be listed in the GROUP BY or sit inside an aggregate. Try to sneak seat into the bookings-per-screening query and the database refuses to run it.

The refusal is not pedantry — ask what the database would have to do. The screening-1 pile contains four bookings with four different seats: G7, G8, C4, F5. The answer has one row for that pile. Which seat should the row show? There is no honest answer, so the database refuses to guess. Grouped columns are safe to show because every row in a pile shares their value; aggregated columns are safe because the pile was summarized into one value. Everything else is ambiguous, and the iron rule is simply the database declining to invent data.

COUNT(*) vs COUNT(column)

One last distinction, small-looking and interview-famous. COUNT(*) counts rows. COUNT(email) counts non-NULL values in the email column — rows where email is missing simply aren't counted. On the Marquee's customers table the two disagree by exactly the three regulars with no email on file:

SELECT COUNT(*) AS all_customers,
       COUNT(email) AS customers_with_email
FROM customers;

That gap is Chapter 2's NULL lesson paying rent: NULL means unknown, and an aggregate honestly refuses to count what it doesn't know. When you want "how many rows", write COUNT(*) and mean it; when you want "how many rows actually have this value", count the column deliberately. Knowing which one you're asking for is the entire trick.