Calculate Percent Of Total With A Window Function

Use `SUM(...) OVER ()` to compare each row's value to the total without collapsing the result set.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Show each category as a share of total sales

The total sales amount is 200. Music contributes 100 of 200, or 50%. Games contributes 30%, and Books contributes 20%. The window function keeps each original row while making the total available to every row.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE sales (category VARCHAR(20), amount INT);

INSERT INTO
  sales (category, amount)
VALUES
  ('Books', 40),
  ('Games', 60),
  ('Music', 100);

Validated query Shared across supported engines.

SQL

SELECT
  category,
  amount,
  ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM
  sales
ORDER BY
  amount DESC;

Expected result Returned rows for the shared example.

category	amount	pct_of_total
Music	100	50
Games	60	30
Books	40	20

All supported engines use the same window-function pattern here and return the same percentages.

Useful when

Where this command helps.

showing each category's contribution to a total
adding percent-of-total metrics to a report without a separate aggregate query

Explanation

What the command is doing.

A percent-of-total calculation is a good fit for window functions because you usually want to keep each row while also referencing the grand total. SUM(amount) OVER () computes that total once across the full result set, and each row can divide its own value by the shared total to produce a percentage.

Calculate Percent Of Total With A Window Function

Show each category as a share of total sales

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.