AVG function in SQL - Step-by-Step Execution

Concept Flow - AVG function

Start with a column of numbers

↓

Sum all values in the column

↓

Count how many values are in the column

↓

Divide sum by count

↓

Return the average value

The AVG function adds all numbers in a column, counts them, then divides the sum by the count to find the average.

Execution Sample

SQL

SELECT AVG(score) FROM tests;

This query calculates the average of all values in the 'score' column from the 'tests' table.

Execution Table

Step	Action	Intermediate Result	Explanation
1	Read all 'score' values	[80, 90, 70, 100]	Collect all scores from the table
2	Sum all scores	80 + 90 + 70 + 100 = 340	Add all scores together
3	Count number of scores	4	There are 4 scores total
4	Divide sum by count	340 / 4 = 85	Calculate average score
5	Return result	85	Final average value returned by AVG function

💡 All scores processed, average calculated and returned

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
scores	empty	[80, 90, 70, 100]	[80, 90, 70, 100]	[80, 90, 70, 100]	[80, 90, 70, 100]	[80, 90, 70, 100]
sum	0	0	340	340	340	340
count	0	0	0	4	4	4
average	undefined	undefined	undefined	undefined	85	85

Key Moments - 2 Insights

Why does AVG divide the sum by the count of values, not by the total number of rows in the table?

AVG divides by the count of values in the column because some rows might have NULL or missing values that are not counted. See execution_table step 3 where count is the number of actual scores.

What happens if the column has no values (empty)?

If there are no values, AVG returns NULL because dividing by zero is not possible. This is why counting values (step 3) is important before dividing.

Visual Quiz - 3 Questions

Look at the execution_table, what is the sum of the scores at step 2?

A 4

B 85

C 340

D 80

💡 Hint

Check the 'Intermediate Result' column at step 2 in the execution_table.

At which step does the AVG function count how many values are in the column?

A Step 1

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the step where 'Count number of scores' is described in the execution_table.

If one score was NULL and ignored, how would the count change in variable_tracker?

A Count would decrease

B Count would stay the same

C Count would increase

D Sum would become zero

💡 Hint

Refer to the 'count' row in variable_tracker and think about how NULL values affect counting.

Concept Snapshot

AVG(column) calculates the average of numeric values in a column.
It sums all non-NULL values and divides by their count.
NULL values are ignored in both sum and count.
Returns NULL if no values exist.
Used to find mean values in data.

Full Transcript

The AVG function in SQL calculates the average value of a numeric column. It works by first reading all the values in the column, then summing them up. Next, it counts how many values there are, ignoring any NULLs. Finally, it divides the sum by the count to get the average. If there are no values, AVG returns NULL. This process helps find the mean value of data in a table.