Percent of total with window functions in SQL - Step-by-Step Execution

Concept Flow - Percent of total with window functions

Start with table data

↓

Calculate sum of all values using window function

↓

Calculate percent of total for each row

↓

Return result with percent column

This flow shows how SQL uses a window function to calculate the total sum and then computes each row's percent of that total.

Execution Sample

SQL

SELECT category, sales,
       sales * 100.0 / SUM(sales) OVER () AS percent_of_total
FROM sales_data;

This query calculates each category's sales as a percent of the total sales.

Execution Table

Step	Row	category	sales	SUM(sales) OVER ()	percent_of_total	Action
1	1	Books	100	500	100 * 100.0 / 500 = 20.0	Calculate total sum 500, compute percent 20.0%
2	2	Electronics	150	500	150 * 100.0 / 500 = 30.0	Compute percent 30.0%
3	3	Clothing	50	500	50 * 100.0 / 500 = 10.0	Compute percent 10.0%
4	4	Toys	200	500	200 * 100.0 / 500 = 40.0	Compute percent 40.0%
5	End	-	-	-	-	All rows processed, query returns result

💡 All rows processed, percent_of_total calculated for each row using total sum 500

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	Final
category	-	Books	Electronics	Clothing	Toys	-
sales	-	100	150	50	200	-
SUM(sales) OVER ()	-	500	500	500	500	500
percent_of_total	-	20.0	30.0	10.0	40.0	-

Key Moments - 2 Insights

Why does SUM(sales) OVER () return the same total for every row?

Because the window function SUM() OVER () calculates the total sum across all rows without grouping, so it repeats the same total value for each row as shown in execution_table rows 1 to 4.

How is percent_of_total calculated for each row?

It multiplies the row's sales by 100 and divides by the total sum from the window function, as shown in execution_table where percent_of_total = sales * 100 / 500.

Visual Quiz - 3 Questions

Look at the execution table, what is the percent_of_total for the 'Clothing' category?

A 20.0

B 10.0

C 30.0

D 40.0

💡 Hint

Check the row where category is 'Clothing' in the execution_table under percent_of_total column.

At which step does the total sum of sales get calculated?

A Step 1

B Step 3

C It is calculated once and used in all steps

D Step 5

💡 Hint

Look at the SUM(sales) OVER () column in execution_table; it shows the same total 500 for all rows.

If the sales for 'Toys' changed to 100, what would be the new percent_of_total for 'Toys'?

A 25.0

B 40.0

C 20.0

D 50.0

💡 Hint

Recalculate percent_of_total as sales * 100 / total sum; total sum changes to 100+150+50+100=400.

Concept Snapshot

Use SUM(column) OVER () to get total sum across all rows.
Calculate percent_of_total as (value * 100) / total sum.
Window function repeats total sum for each row.
No GROUP BY needed, keeps all rows.
Useful for showing each part's share of whole.

Full Transcript

This visual execution shows how to calculate the percent of total for each row using SQL window functions. The query uses SUM(sales) OVER () to get the total sales sum repeated on every row. Then it calculates each row's percent by dividing its sales by the total sum and multiplying by 100. The execution table traces each row's category, sales, total sum, and percent_of_total step by step. The variable tracker shows how values change or stay the same across rows. Key moments clarify why the total sum repeats and how the percent is computed. The quiz tests understanding of the percent values, when total sum is calculated, and how changing data affects results. This method is simple and powerful for showing parts of a whole in SQL without grouping rows.

Practice

1. What does the SQL window function SUM(column) OVER () compute in a query?

easy

A. The maximum value of the column

B. The total sum of the column across all rows without grouping

C. The average of the column values

D. The sum of the column for each group defined by GROUP BY

Solution

1. Step 1: Understand window function syntax

   The syntax SUM(column) OVER () means sum over all rows without partitioning or grouping.

2. Step 2: Compare with GROUP BY sum

   Unlike GROUP BY, this sums the entire column across all rows, returning the same total for each row.

3. Final Answer:

   The total sum of the column across all rows without grouping -> Option B

4. Quick Check:

   SUM() OVER () = total sum [OK]

Hint: SUM() OVER () gives total sum for all rows, no grouping [OK]

Common Mistakes:

• Confusing window sum with GROUP BY sum
• Thinking it calculates average
• Assuming it returns max value

2. Which of the following SQL queries correctly calculates the percent of total sales per product using window functions?

easy

A. SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table;

B. SELECT product, sales, (SUM(sales) OVER () / sales) * 100 AS percent_total FROM sales_table;

C. SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table GROUP BY product;

D. SELECT product, sales, (sales / COUNT(*) OVER ()) * 100 AS percent_total FROM sales_table;

Solution

1. Step 1: Identify correct window function usage

   SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; uses SUM(sales) OVER () to get total sales across all rows, then divides each sales value by this total.

2. Step 2: Check other options for errors

   SELECT product, sales, (SUM(sales) OVER () / sales) * 100 AS percent_total FROM sales_table; divides total by sales (wrong direction). SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table GROUP BY product; uses SUM(sales) without OVER(), which requires GROUP BY and won't work as intended. SELECT product, sales, (sales / COUNT(*) OVER ()) * 100 AS percent_total FROM sales_table; divides sales by count of rows, not sum.

3. Final Answer:

   SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; -> Option A

4. Quick Check:

   Percent = sales / total sales * 100 [OK]

Hint: Divide sales by SUM() OVER () and multiply by 100 [OK]

Common Mistakes:

• Reversing numerator and denominator
• Using SUM() without OVER() in window context
• Using COUNT() instead of SUM() for total

3. Given the table sales_data with columns product and amount, what is the output of this query?

SELECT product, amount, ROUND((amount / SUM(amount) OVER ()) * 100, 1) AS percent_total FROM sales_data ORDER BY product;

Assume the data:
product | amount
-------|-------
A | 50
B | 30
C | 20

medium

A. [{"product": "A", "amount": 50, "percent_total": 100.0}, {"product": "B", "amount": 30, "percent_total": 100.0}, {"product": "C", "amount": 20, "percent_total": 100.0}]

B. [{"product": "A", "amount": 50, "percent_total": 33.3}, {"product": "B", "amount": 30, "percent_total": 20.0}, {"product": "C", "amount": 20, "percent_total": 13.3}]

C. [{"product": "A", "amount": 50, "percent_total": 25.0}, {"product": "B", "amount": 30, "percent_total": 15.0}, {"product": "C", "amount": 20, "percent_total": 10.0}]

D. [{"product": "A", "amount": 50, "percent_total": 50.0}, {"product": "B", "amount": 30, "percent_total": 30.0}, {"product": "C", "amount": 20, "percent_total": 20.0}]

Solution

1. Step 1: Calculate total amount

   Total = 50 + 30 + 20 = 100.

2. Step 2: Calculate percent for each product

   Percent = (amount / total) * 100 rounded to 1 decimal:
   A: (50/100)*100 = 50.0
   B: (30/100)*100 = 30.0
   C: (20/100)*100 = 20.0

3. Final Answer:

   [{"product": "A", "amount": 50, "percent_total": 50.0}, {"product": "B", "amount": 30, "percent_total": 30.0}, {"product": "C", "amount": 20, "percent_total": 20.0}] -> Option D

4. Quick Check:

   Percent totals sum to 100% [OK]

Hint: Sum amounts, divide each by total, multiply by 100 [OK]

Common Mistakes:

• Not rounding results
• Using COUNT() instead of SUM()
• Calculating percent incorrectly (e.g., reversed division)

4. You wrote this query to calculate percent of total sales:

SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table;

But it gives an error. What is the problem?

medium

A. SUM(sales) requires a GROUP BY clause or window function syntax

B. sales column does not exist

C. Multiplying by 100 is invalid syntax

D. Missing WHERE clause

Solution

1. Step 1: Analyze use of SUM() without OVER()

   SUM(sales) without GROUP BY or window function context causes error because SQL doesn't know how to aggregate without grouping.

2. Step 2: Correct usage

   Use either GROUP BY product with SUM(sales) or use window function SUM(sales) OVER () to get total sum without grouping.

3. Final Answer:

   SUM(sales) requires a GROUP BY clause or window function syntax -> Option A

4. Quick Check:

   SUM() needs GROUP BY or OVER() [OK]

Hint: Use SUM() OVER () or GROUP BY with SUM() [OK]

Common Mistakes:

• Ignoring need for GROUP BY with aggregate functions
• Assuming multiplication by 100 causes error
• Thinking WHERE clause is mandatory here

5. You have a table orders with columns category and amount. Write a query to show each category, total amount per category, and percent of total amount across all categories. Which query is correct?

hard

A. SELECT category, SUM(amount) AS category_total, (SUM(amount) / COUNT(*) OVER ()) * 100 AS percent_total FROM orders GROUP BY category;

B. SELECT category, amount, (amount / SUM(amount) OVER ()) * 100 AS percent_total FROM orders ORDER BY category;

C. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category;

D. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(amount)) * 100 AS percent_total FROM orders GROUP BY category;

Solution

1. Step 1: Calculate total per category

   Use SUM(amount) GROUP BY category to get total per category.

2. Step 2: Calculate percent of total across all categories

   Use window function SUM(SUM(amount)) OVER () to get grand total across all categories, then divide category total by this and multiply by 100.

3. Step 3: Verify query correctness

   SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category; correctly combines GROUP BY with window function for total sum. SELECT category, amount, (amount / SUM(amount) OVER ()) * 100 AS percent_total FROM orders ORDER BY category; uses amount per row, not category total. SELECT category, SUM(amount) AS category_total, (SUM(amount) / COUNT(*) OVER ()) * 100 AS percent_total FROM orders GROUP BY category; incorrectly uses COUNT() for total. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(amount)) * 100 AS percent_total FROM orders GROUP BY category; divides sum by itself, always 100%.

4. Final Answer:

   SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category; -> Option C

5. Quick Check:

   Percent = category sum / total sum * 100 [OK]

Hint: Use GROUP BY with SUM() and window SUM(SUM(amount)) OVER () for total [OK]

Common Mistakes:

• Using row amount instead of category sum
• Using COUNT() instead of SUM() for total
• Dividing sum by itself always gives 100%