Percent of Total in SQL: Share of Sales per Region
Work out each region's share of total sales with SUM OVER (), round it to one decimal, and avoid the whole-number division trap that quietly drops the decimals, on a sample online shop database.
Step by step
Share of total
- What share?. North sold the most, but what share of all sales is that? To get percent of total, divide each group's figure by the grand total. We build it in 3 steps on a sample online shop database.
- Region is in customers. The order totals are in the orders table, but each customer's region is in the customers table. So the query needs both tables.
Sales per region
- Sales per region. First, get sales per region. Join orders to customers with USING (customer_id), which is a short way to join on a column both tables share. Then GROUP BY region and sort with ORDER BY sales DESC so the biggest comes first. Run it and you get 4 regions. North is at the top with $5,373.50 in sales.
SELECT region, SUM(order_total) AS sales FROM orders JOIN customers USING (customer_id) GROUP BY region ORDER BY sales DESC;
Grand total
- The grand total. Now add the grand total: SUM(SUM(order_total)) OVER (). The inner SUM is each region's sales. OVER with empty brackets makes the window every row of the result, so the outer SUM adds up all regions. Run it. Every row now shows the same total, $15,298, beside its own region's sales.
AS sales, SUM(SUM(order_total)) OVER () AS total
The percent
- The percent. Now swap the total for the percent: 100.0 times the region's sales, divided by the grand total. ROUND with 1 keeps 1 decimal place. Name the column pct. North is 35.1% of all sales and West is the smallest at 16.9%. Here the 4 shares add up to 100.
ROUND(100.0 * SUM(order_total) / SUM(SUM(order_total)) OVER (), 1) AS pct
The decimal trap
- The decimal trap. Now a trap. Clear the editor and work out each order status's share of orders. COUNT(*) counts the orders and SUM(COUNT(*)) OVER () gives the total count. This time, write 100 without the .0. Delivered shows 78 with no decimals, and that number is wrong. Counts are whole numbers, and in this database a whole number divided by a whole number drops the decimals before ROUND ever sees them. Change 100 * to 100.0 *. With one decimal number in the calculation, SQL does the division with decimals. Run it again. Delivered is now 78.7%, the real share of delivered orders.
SELECT status, COUNT(*) AS orders, ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct FROM orders GROUP BY status;100.0 *
Recap
- Percent of total. To sum up: SUM(...) OVER () puts the grand total on every row. Multiply each group by 100.0, divide it by that total, and round.
Common mistakes
- Writing 100 instead of 100.0. In databases such as SQLite and SQL Server, an integer divided by an integer gives an integer, so 78.7 becomes 78. Write 100.0 so the division keeps its decimals.
- Using SUM(order_total) OVER () in a grouped query. After GROUP BY, the window works on the grouped rows, so it needs the grouped sum inside it: SUM(SUM(order_total)) OVER (). A single SUM gives an error or a wrong total, depending on the database.
- Forgetting that WHERE changes the total. OVER () only sees the rows left after WHERE. If you filter to one year, each share is a share of that year's total, not of all sales.
- Expecting rounded shares to always add up to exactly 100. Rounding each share to 1 decimal can make the total 99.9 or 100.1. Tell readers it is a rounding difference, or show more decimals.
When a business analyst uses this
Before a prioritisation workshop, run the COUNT(*) version on the defect or support-ticket table, grouped by category. A table showing each category's share of all tickets shows the group where most problems come from, and backs up the case for putting its requirements first.
Questions people ask
How do you calculate percentage of total in SQL?
Divide each value by the grand total and multiply by 100.0. SUM(...) OVER () gives the grand total on every row. In databases without window functions, such as MySQL 5.7, divide by a subquery like (SELECT SUM(order_total) FROM orders) instead.
How do you calculate percentage of total with GROUP BY in SQL?
Group as usual, then divide each group's sum by SUM(SUM(order_total)) OVER (), which repeats the grand total on every grouped row. Multiply by 100.0 and use ROUND(..., 1) for 1 decimal.
How do you show a count as a percentage of total in SQL?
Use 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () with GROUP BY. Keep the .0, because in databases such as SQLite and SQL Server, a whole number divided by a whole number loses its decimals.
