Practical SQLite Workflows

Summary Table

Materialize a Report

CREATE TABLE AS SELECT can materialize a reusable report table from detail rows.

Program

Play the script to choose a report region and build a summary table for that slice.

wanted_region
summary_table.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);
CREATE TABLE region_summary (region TEXT, total INTEGER);
WITH params(wanted_region) AS (VALUES ('West')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
SELECT region, total FROM region_summary ORDER BY region;

CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);
CREATE TABLE region_summary (region TEXT, total INTEGER);
WITH params(wanted_region) AS (VALUES ('East')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
SELECT region, total FROM region_summary ORDER BY region;

  1. tables ← 1 row

    1CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);3CREATE TABLE region_summary (region TEXT, total INTEGER);
    values this step4 rowssales

  3. tables ← 2 rows

    2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);3CREATE TABLE region_summary (region TEXT, total INTEGER);4WITH params(wanted_region) AS (VALUES ('West')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step2 rowstables

  4. region_summary ← 1 row

    3CREATE TABLE region_summary (region TEXT, total INTEGER);4WITH params(wanted_region) AS (VALUES ('West')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;5SELECT region, total FROM region_summary ORDER BY region;
    values this step1 rowregion_summary

  5. result ← 1 row

    4WITH params(wanted_region) AS (VALUES ('West')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;5SELECT region, total FROM region_summary ORDER BY region;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);3CREATE TABLE region_summary (region TEXT, total INTEGER);
    values this step4 rowssales

  3. tables ← 2 rows

    2INSERT INTO sales VALUES ('West', 'book', 120), ('West', 'tool', 80), ('East', 'book', 90), ('East', 'game', 130);3CREATE TABLE region_summary (region TEXT, total INTEGER);4WITH params(wanted_region) AS (VALUES ('East')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step2 rowstables

  4. region_summary ← 1 row

    3CREATE TABLE region_summary (region TEXT, total INTEGER);4WITH params(wanted_region) AS (VALUES ('East')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;5SELECT region, total FROM region_summary ORDER BY region;
    values this step1 rowregion_summary

  5. result ← 1 row

    4WITH params(wanted_region) AS (VALUES ('East')) INSERT INTO region_summary SELECT region, SUM(amount) AS total FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;5SELECT region, total FROM region_summary ORDER BY region;
    values this step1 rowresult
summary table A dedicated table stores the report output for later reads.
materialized report The summary table keeps the selected report result for later reads.
slice The parameter CTE chooses which region becomes the materialized slice.