Reporting Queries

Grouped Report

Sales by Region

GROUP BY turns detail rows into report rows. A parameter CTE can choose which region to show in the report.

Program

Play the script to choose a region and see its grouped sales total and order count.

wanted_region
region_sales_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);
WITH params(wanted_region) AS (VALUES ('West')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;

CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);
WITH params(wanted_region) AS (VALUES ('East')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;

  1. tables ← 1 row

    1CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);3WITH params(wanted_region) AS (VALUES ('West')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step4 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);3WITH params(wanted_region) AS (VALUES ('West')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (order_id INTEGER, region TEXT, amount INTEGER);2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);3WITH params(wanted_region) AS (VALUES ('East')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step4 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES (1, 'West', 120), (2, 'East', 90), (3, 'West', 80), (4, 'East', 160);3WITH params(wanted_region) AS (VALUES ('East')) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
    values this step1 rowresult
GROUP BY `GROUP BY region` collapses matching detail rows into one report row per region.
SUM `SUM(amount)` adds the selected row amounts.
COUNT `COUNT(*)` counts the selected detail rows.