A dashboard filter changes the totals but not the '% of total' column — fix the aggregation-scope bug.
A sales dashboard has a region filter. When a region is selected, the total column updates correctly, but the pct_of_total column stays unchanged and the column no longer sums to 100%.
Constraints: Postgres; the region filter is the WHERE clause shown; pct_of_total should be each category's share of the currently filtered total.
SELECT
category,
SUM(amount) AS total,
100.0 * SUM(amount) / (SELECT SUM(amount) FROM sales) AS pct_of_total
FROM sales
WHERE region = 'EU'
GROUP BY category;Find and fix the bug so the percentages respect the filter.
The denominator is an unfiltered subquery — (SELECT SUM(amount) FROM sales) ignores the WHERE the numerator honors — so a filter shifts each numerator but not the grand total, and the column stops summing to 100%. Fix with SUM(SUM(amount)) OVER (), evaluated after WHERE.
- ✗Assuming a subquery inherits the outer query's
WHEREfilter - ✗Blaming rounding when the denominator scope is wrong
- ✗Adding columns to
GROUP BYinstead of scoping the total
- →Why does
SUM(SUM(amount)) OVER ()respect the filter when the subquery doesn't? - →How would you make the denominator the per-region total instead of the grand total?
The numerator SUM(amount) is computed over rows after WHERE region = 'EU', but the denominator (SELECT SUM(amount) FROM sales) is an independent subquery with no filter, so it always equals the whole-table sum. Selecting a region shrinks the numerators but not the denominator, and the column stops summing to 100%. The window function SUM(SUM(amount)) OVER () is evaluated after WHERE and GROUP BY, over the same filtered and grouped rows:
SELECT
category,
SUM(amount) AS total,
100.0 * SUM(amount) / SUM(SUM(amount)) OVER () AS pct_of_total
FROM sales
WHERE region = 'EU'
GROUP BY category;Now both numerator and denominator are bounded by the filter, and the column sums to 100% again. When the region parameter changes, both recompute consistently, with no second pass over the table.
