Reporting Windows

Group Share

Percent of a Reporting Bucket

A window total can show each row's contribution to its department or category without a separate summary query.

Program

Play the script to choose a department and calculate each expense row's share of that department total.

wanted_department
group_share_window.sql
Replay: real traced execution (multi-file project)
CREATE TABLE report_choice AS WITH params(wanted_department) AS (VALUES ('Ops')) SELECT wanted_department FROM params;
CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);
INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);
SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;

CREATE TABLE report_choice AS WITH params(wanted_department) AS (VALUES ('Sales')) SELECT wanted_department FROM params;
CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);
INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);
SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_department) AS (VALUES ('Ops')) SELECT wanted_department FROM params;2CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);
    values this step1 rowchoice

  2. expenses ← 6 rows

    2CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);3INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);4SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;
    values this step6 rowsexpenses

  3. result ← 3 rows

    3INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);4SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_department) AS (VALUES ('Sales')) SELECT wanted_department FROM params;2CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);
    values this step1 rowchoice

  2. expenses ← 6 rows

    2CREATE TABLE expenses (department TEXT, item TEXT, amount INTEGER);3INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);4SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;
    values this step6 rowsexpenses

  3. result ← 3 rows

    3INSERT INTO expenses VALUES ('Ops', 'cloud', 60), ('Ops', 'support', 40), ('Ops', 'tools', 20), ('Sales', 'travel', 50), ('Sales', 'events', 30), ('Sales', 'crm', 20);4SELECT item, amount, SUM(amount) OVER (PARTITION BY department) AS department_total, ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_department FROM expenses WHERE department = (SELECT wanted_department FROM report_choice) ORDER BY amount DESC, item;
    values this step3 rowsresult
window total `SUM(amount) OVER (PARTITION BY department)` repeats the department total on each row.
percentage The row amount divided by the window total gives a percent contribution.
report bucket The selector changes which department bucket the report displays.