Grouping with GROUP BY

One row per account, per rep or per month, with totals for each.

Lesson 11 returned one row for the whole table. GROUP BY returns one row per group.

SELECT stage,
       COUNT(*)      AS deals,
       SUM(amount)   AS total_value
FROM opportunities
GROUP BY stage;

The database sorts rows into a pile for each stage, then runs COUNT and SUM on each pile.

The one rule

Every column in SELECT must either be inside an aggregate or listed in GROUP BY.

-- Error: which name should the database show for each stage?
SELECT stage, name, COUNT(*)
FROM opportunities
GROUP BY stage;

Each stage has many deal names, and the group only gets one row. Either add name to GROUP BY (one row per stage and name) or remove it.

SQLite is the exception: it runs this query and shows a name from an arbitrary row. That’s worse than an error, because the result looks right. Follow the rule anyway.

Grouping by more than one column

SELECT owner_id,
       stage,
       COUNT(*) AS deals
FROM opportunities
GROUP BY owner_id, stage
ORDER BY owner_id, stage;

One row for every rep and stage combination that exists in the data.

Say the grain out loud

Before you write GROUP BY, finish this sentence: “One row of my result is one ___.” One stage. One rep per stage. One account. If you can’t say it, the query isn’t ready.

Filter first, then group

WHERE runs before GROUP BY (lesson 3), so it decides which rows go into the piles:

SELECT owner_id,
       COUNT(*)      AS open_deals,
       SUM(amount)   AS open_pipeline
FROM opportunities
WHERE closed_at IS NULL
GROUP BY owner_id
ORDER BY open_pipeline DESC;

ORDER BY can use the alias open_pipeline because it runs after SELECT.

Common mistake: groups only exist if rows exist. A rep with no open deals won’t appear in the result at all, not even with a 0. Lesson 15 shows how to keep them.

Try it

How many accounts are in each industry? Return industry and the count, largest industry first. Field guide exercise B2 is the same question.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson