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.