Analytical Query Patterns

Percent of Total

Compare to the Whole

Window aggregates can keep each detail row while also exposing the grand total needed for a percentage calculation.

Program

Play the script to choose a category and see its amount as a percentage of total sales.

wanted_category
percent_of_total.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
WITH params(wanted_category) AS (VALUES ('Books')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
WITH params(wanted_category) AS (VALUES ('Tools')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
WITH params(wanted_category) AS (VALUES ('Games')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
    values this step1 rowtables

  2. sales ← 3 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Books')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step3 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Books')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
    values this step1 rowtables

  2. sales ← 3 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Tools')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step3 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Tools')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
    values this step1 rowtables

  2. sales ← 3 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Games')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step3 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);3WITH params(wanted_category) AS (VALUES ('Games')), totals AS (SELECT category, amount, SUM(amount) OVER () AS grand_total FROM sales) SELECT category, amount, ROUND(100.0 * amount / grand_total, 1) AS pct_total FROM totals WHERE category = (SELECT wanted_category FROM params);
    values this step1 rowresult
window aggregate `SUM(amount) OVER ()` computes the grand total while keeping every category row.
percentage `ROUND(100.0 * amount / grand_total, 1)` formats a one-decimal percentage.
detail plus total The query can filter to one category after computing the all-row total.