Hack University
Course outline · 0% complete

0/29 lessons0%

Course overview →

GROUP BY: One Answer per Group

lesson 4-2 · ~12 min · 9/29

The GROUP BY mental model

SUM(price) WHERE customer = 'Ana' gave one customer's total. What about every customer's total, in one query? That is GROUP BY:

SELECT customer, COUNT(*), SUM(price)
FROM orders
GROUP BY customer
ORDER BY customer;

Picture it in three steps:

  1. Sort rows into buckets. Every row with the same customer value lands in the same bucket.
  2. Aggregate each bucket separately. COUNT and SUM run once per bucket, not once overall.
  3. Output one row per bucket.

So the query returns one line per customer: their name, their number of orders, their total spend.

orders (6 rows)Ana · coffee · 5Ben · sandwich · 8Ana · bagel · 4Cara · coffee · 5Ben · coffee · 5Ana · sandwich · 8GROUP BYAnacoffee 5 · bagel 4sandwich 8Bensandwich 8 · coffee 5Caracoffee 5SUMAna|3|17Ben|2|13Cara|1|5one bucket per customer → one output row each
GROUP BY customer sorts the six rows into three buckets, then COUNT and SUM run once per bucket, producing one output row per customer.

Code exercise · sql

Run it. One output row per customer: name, order count, total spent. Compare with the figure.

Quiz

In `SELECT customer, SUM(price) FROM orders GROUP BY customer;`, what does SUM(price) add up?

Code exercise · sql

Your turn. Count how many times each ITEM was ordered. One row per item, alphabetical order.

Code exercise · sql

Drill. What is the average price of each item? One row per item, alphabetical, with the average rounded to 2 decimals. (Each item here always costs the same, so the averages look plain, but the query shape is the everyday one.)