Aggregates

Group By

Totals Per Category

GROUP BY runs aggregates separately for each group.

Program

Play the query to summarize sales per item.

group_by.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (item TEXT, qty INTEGER);
INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);
SELECT item, SUM(qty) AS sold FROM sales GROUP BY item ORDER BY item;

  1. tables ← 1 row

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);
    values this step1 rowtables

  2. sales ← 3 rows

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);3SELECT item, SUM(qty) AS sold FROM sales GROUP BY item ORDER BY item;
    values this step3 rowssales

  3. result ← 2 rows

    2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);3SELECT item, SUM(qty) AS sold FROM sales GROUP BY item ORDER BY item;
    values this step2 rowsresult

Make One Total Per Item

  1. The rows include two book sales and one pen sale.
  2. GROUP BY item puts matching item names together.
  3. SUM(qty) adds quantities inside each group.
  4. ORDER BY item prints book before pen. | Item group | Rows included | Sold | | --- | --- | --- | | book | 2 and 3 | 5 | | pen | 5 | 5 |
GROUP BY `GROUP BY item` creates one group per item value.
aggregate per group `SUM(qty)` runs inside each group.
ORDER BY Sorting makes grouped output easy to compare.

Exercise: group_by.sql

Group sales by item, sum quantity per item, and sort the grouped rows by item