AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 2
SQL ยท Lesson 98 of 126

Percentages of a Total

SUM() OVER ().

โฑ 2 min readโ–ถ Video at 2:18:21๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

What share of all orders came in each month? To work out a percentage, every row needs to know the grand total. A window function with empty brackets gives exactly that.

Percentages of a Total
From the video: Percentages of a Total
  • What share of all orders?
  • Each row needs the total
  • OVER with empty brackets

SUM() OVER ()

Sum of orders, over, with nothing inside the brackets, means, add up every row in the result. So each month is divided by the grand total. Run it. August twenty twenty five had seven and a half percent of all orders, over two years.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
    FROM orders
    GROUP BY month
)
SELECT month,
       orders,
       ROUND(100.0 * orders / SUM(orders) OVER (), 1) AS pct_of_all
FROM monthly
ORDER BY orders DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  month  โ”‚ orders โ”‚ pct_of_all โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2025-08 โ”‚ 120    โ”‚ 7.5        โ”‚
โ”‚ 2025-12 โ”‚ 104    โ”‚ 6.5        โ”‚
โ”‚ 2025-05 โ”‚ 103    โ”‚ 6.4        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The integer trap

Notice we wrote one hundred point zero, not one hundred. Here's why. Run it. One hundred and forty divided by one thousand five hundred and ninety eight gives zero, because whole numbers divide into whole numbers in many databases. Add a decimal point, and you get the real answer, about eight point eight percent.

query.sqlsql
SELECT 140 / 1598         AS integer_division,
       100.0 * 140 / 1598 AS percentage;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ integer_division โ”‚    percentage    โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 0                โ”‚ 8.76095118898623 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Cancellation rate

Your turn. What percentage of all orders were cancelled? Round to one decimal place. Pause and try. Here's a solution. Count the cancelled orders with case inside sum, times one hundred point zero, divided by count star. Eight point eight percent.

โœ๏ธ Exercise: Cancellation rate

Percentage of orders that were cancelled

Show hint

100.0 * SUM(CASE ...) / COUNT(*)

Show solution
solution.sqlsql
SELECT ROUND(100.0 * SUM(CASE WHEN status = 'cancelled'
                              THEN 1 ELSE 0 END) / COUNT(*), 1)
         AS cancel_rate
FROM orders;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ cancel_rate โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 8.8         โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Sum over empty brackets gives the grand total on every row. And always use one hundred point zero for percentages.

Key takeaways
  • SUM(x) OVER ()
  • 100.0, not 100
  • Round the result
Next up: Time Between Orders โ€” LAG with PARTITION BY