Aggregates

Count and Sum

Whole-Table Totals

Aggregate functions collapse many rows into one summary row.

Program

Play the query to count orders and sum revenue.

count_sum.sql
Replay: real traced execution (multi-file project)
CREATE TABLE orders (id INTEGER, total INTEGER);
INSERT INTO orders VALUES (1, 12), (2, 30), (3, 8);
SELECT COUNT(*) AS orders, SUM(total) AS revenue FROM orders;

  1. tables ← 1 row

    1CREATE TABLE orders (id INTEGER, total INTEGER);2INSERT INTO orders VALUES (1, 12), (2, 30), (3, 8);
    values this step1 rowtables

  2. orders ← 3 rows

    1CREATE TABLE orders (id INTEGER, total INTEGER);2INSERT INTO orders VALUES (1, 12), (2, 30), (3, 8);3SELECT COUNT(*) AS orders, SUM(total) AS revenue FROM orders;
    values this step3 rowsorders

  3. result ← 1 row

    2INSERT INTO orders VALUES (1, 12), (2, 30), (3, 8);3SELECT COUNT(*) AS orders, SUM(total) AS revenue FROM orders;
    values this step1 rowresult

Collapse Rows to One Summary

  1. The table has three order rows.
  2. COUNT(*) counts all three rows.
  3. SUM(total) adds 12 + 30 + 8.
  4. The query returns one summary row. | Order id | Total | | --- | --- | | 1 | 12 | | 2 | 30 | | 3 | 8 | | summary | orders=3, revenue=50 |
COUNT `COUNT(*)` counts rows.
SUM `SUM(total)` adds a numeric column.
alias `AS revenue` names the output column.

Exercise: count_sum.sql

Count all order rows, sum their totals, and name the summary columns