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.

region_sales_report.sql 
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 ()) 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 ()) SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM sales WHERE region = (SELECT wanted_region FROM params) GROUP BY region;
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.