Share of total
Show each group as a percentage of the grand total using a window function.
SELECT customer_id,
SUM(amount) AS amount,
ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;How it works
The inner SUM(amount) is calculated for each customer by GROUP BY. SUM(...) OVER () then adds those group totals together across the whole result, which is the grand total.
Dividing one by the other and multiplying by 100.0 gives each customer's share. The WHERE clause is applied before both, so the shares add up to 100 for the paid orders.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
customer_id | amount | pct_of_total
1 | 500.0 | 37.9
2 | 400.0 | 30.3
3 | 420.0 | 31.8Common mistakes and limitations
- Using 100 instead of 100.0 can make some databases do whole-number division and return 0. - Filtering the percentage rows afterwards (for example keeping only the top customer) changes the denominator if you filter before the window function runs. - Nesting an aggregate inside a window function is not allowed in every database. If yours refuses it, calculate the group totals in a CTE first.