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.

group_share_window.sql 
CREATE TABLE report_choice AS WITH params(wanted_department) AS (VALUES ()) 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 ()) 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;
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.