How does the percent of total query work?

GROUP BY category gives one row and one SUM per category. SUM(SUM(total_amount)) OVER () adds up those category sums across all rows, because an empty OVER () means the whole result set. Dividing the category sum by that grand total gives the share; the shares add up to 1.

What commonly goes wrong with percent of total in SQL?

Filtering rows in a later step changes the total; the share is only of the rows that reach the window.

Percent of Total in SQL

Percent of total shows each group's share of the overall figure, such as each category's share of sales.

Example query

This example assumes: order_items(id, order_id bigint, category text, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT category,
       SUM(total_amount) AS sales,
       SUM(total_amount) / NULLIF(SUM(SUM(total_amount)) OVER (), 0) AS share_of_total
FROM order_items
GROUP BY category
ORDER BY sales DESC;

How it works

GROUP BY category gives one row and one SUM per category.
SUM(SUM(total_amount)) OVER () adds up those category sums across all rows, because an empty OVER () means the whole result set.
Dividing the category sum by that grand total gives the share; the shares add up to 1.

MySQL, BigQuery, SQL Server and SQLite differences

Nested aggregates like SUM(SUM(x)) OVER () work in PostgreSQL, MySQL 8+, SQL Server, BigQuery and SQLite 3.25+.
SQL Server and some others integer-divide when both sides are integers; multiply by 1.0 or cast to a decimal type first.

Common mistakes

Filtering rows in a later step changes the total; the share is only of the rows that reach the window.
Many small groups produce shares too tiny to read; group the tail into 'Other'.
Null categories form their own group; label them rather than letting them disappear in a chart.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

What share of sales does each product category make up?
Show each region's percentage of total revenue.