Summary Table - SQL Programming

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.

summary_table.sql

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 ()) 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 ()) 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;

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.