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.

percent_of_total.sql 
CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('Books', 300), ('Tools', 200), ('Games', 100);
WITH params(wanted_category) AS (VALUES ()), 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 ()), 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 ()), 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);
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.