GROUP BY: One Answer per Group

The GROUP BY mental model

SUM(price) WHERE customer = 'Ana' gave one customer's total, and repeating it by hand for every customer would be tedious and would break the moment a new customer appeared. GROUP BY computes all of them in a single query:

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.

The result is one line per customer: their name, their number of orders, and their total spend. Notice that customer can appear in the SELECT list here, unlike in the previous lesson, precisely because there is now exactly one customer value per output row.

One row per customer

Six order rows collapse into three output rows, one per customer, each carrying that customer's order count and total spend. Compare it against the buckets in the figure above.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

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

Output

Ana|3|17
Ben|2|13
Cara|1|5

Ana's three orders total 17, which matches the single-customer query from lesson 4-1 exactly. The difference is that this one query also produced Ben's and Cara's totals, and it would keep working unchanged if a hundred new customers appeared tomorrow.

The ORDER BY customer at the end sorts the output rows. As always, grouping decides which rows exist, and sorting decides the order they arrive in.

What an aggregate sums under GROUP BY

In SELECT customer, SUM(price) FROM orders GROUP BY customer;, SUM(price) adds up the prices within each customer's bucket, separately.

With GROUP BY in play, every aggregate in the query runs once per bucket rather than once over the whole table. Ana's bucket sums to 17, Ben's to 13, and Cara's to 5.

Remove the GROUP BY and the very same SUM(price) collapses the entire table into one number, 35. The aggregate function did not change at all, only the set of rows it was handed, and 17 + 13 + 5 = 35 confirms that no data went missing either way.

Counting orders per item

Changing the grouping column changes what the buckets mean. Grouping by item instead of customer answers how many times each item was ordered.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT item, COUNT(*)
FROM orders
GROUP BY item
ORDER BY item;

Output

bagel|1
coffee|3
sandwich|2

The word customer was replaced by item in three places: the SELECT list, the GROUP BY, and the ORDER BY. Keeping those in step is the habit to build, because selecting one column while grouping by another is the classic GROUP BY error.

The counts add up to 6, the same six rows as before, simply divided into different buckets. Three coffees, two sandwiches, and one bagel is the sort of result that becomes a best-sellers report in a real application.

Average price per item

AVG groups exactly like COUNT and SUM do. Each item here always costs the same, so the averages come out plain, but the query shape is the everyday one you will reuse constantly.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT item, ROUND(AVG(price), 2) FROM orders
GROUP BY item
ORDER BY item;

Output

bagel|4.0
coffee|5.0
sandwich|8.0

GROUP BY item builds the buckets and AVG runs once inside each one, wrapped in ROUND(..., 2) as lesson 4-1 recommended. The three coffee rows all cost 5, so their average is 5.0, and the single bagel row averages to its own price.

Note that the output shows 4.0 rather than 4. AVG always produces a decimal result, even when the underlying column is an integer and the division comes out even.