AVG function in MySQL - 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 calculates the average by adding all values in a column and dividing by the number of values.

Execution Sample

MySQL

SELECT AVG(score) FROM tests;

This query calculates the average score from the 'tests' table.

Execution Table

Step	Action	Values Involved	Result
1	Identify column 'score' values	[80, 90, 70, 100, 60]	Values collected
2	Sum all values	80 + 90 + 70 + 100 + 60	400
3	Count number of values	5	5
4	Divide sum by count	400 / 5	80
5	Return average	80	80

💡 All values processed; average calculated as 80

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
sum	0	400	400	400	400
count	0	0	5	5	5
average	undefined	undefined	undefined	80	80

Key Moments - 2 Insights

Why does AVG ignore NULL values in the column?

AVG only sums and counts non-NULL values, so NULLs do not affect the sum or count, as shown in the execution_table where only actual numbers are processed.

What happens if the column has zero rows?

If there are no rows, the count is zero, so AVG returns NULL because dividing by zero is undefined. This is not shown in the current table but is important to remember.

Visual Quiz - 3 Questions

Look at the execution table, what is the sum of the 'score' values at Step 2?

A 5

B 80

C 400

D 4000

💡 Hint

Check the 'Result' column at Step 2 in the execution_table.

At which step does the AVG function calculate the final average value?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Look for the division operation in the execution_table.

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

A Count would decrease by 1

B Count would stay the same

C Sum would increase

D Average would be NULL

💡 Hint

Recall that AVG ignores NULLs, so count only includes non-NULL values.

Concept Snapshot

AVG(column_name)
Calculates the average of non-NULL values in a column.
Steps: sum values, count values, divide sum by count.
Returns NULL if no non-NULL values.
Useful for finding mean values in data.

Full Transcript

The AVG function in SQL calculates the average value of a numeric column. It works by first collecting all the non-NULL values in the column, then summing these values, counting how many there are, and finally dividing the sum by the count. For example, if the scores are 80, 90, 70, 100, and 60, the sum is 400 and the count is 5, so the average is 80. AVG ignores NULL values and returns NULL if there are no non-NULL values to average. This process helps find the mean value in a dataset easily.