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? Managers love percent of total. In SQL, you divide each group by the grand total. Let's build it.
- Region is in customers. Orders hold the totals, but the region lives in the customers table. So we need both.
Sales per region
- Sales per region. First, sales per region. Join orders to customers. Using customer ID is a short way to join on a column both tables share. Then group by region, biggest first. Four regions. North sold five thousand three hundred and seventy three fifty.
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 the grand total. Sum of the sales, over empty brackets. Over with nothing inside means the window is every row, so each row gets the total of all regions. Every row now shows the same total. Fifteen thousand two hundred and ninety eight.
AS sales, SUM(SUM(order_total)) OVER () AS total
The percent
- The percent. Now divide. A hundred times the region's sales, divided by the grand total. Round, comma one, keeps one decimal. Call it p c t. North is thirty five point one percent of all sales. West is the smallest, at sixteen point nine. The four shares add up to one hundred.
ROUND(100.0 * SUM(order_total) / SUM(SUM(order_total)) OVER (), 1) AS pct
The decimal trap
- The decimal trap. One trap to know. Let's do the same for counts. What share of orders has each status? This time, write a hundred without the point zero. Delivered shows just seventy eight, with no decimals at all. That number is wrong. Counts are whole numbers, and dividing whole numbers throws the decimals away. Change a hundred to a hundred point zero. Now SQL works with decimals. Seventy eight point seven percent. That's 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. So, sum over empty brackets gives the grand total on every row. Divide each group by it, times a hundred point zero, and round.
