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.

Practice

1. What does the SQL AVG() function do?

easy

A. Calculates the average value of a numeric column

B. Counts the number of rows in a table

C. Finds the maximum value in a column

D. Returns the sum of all values in a column

Solution

1. Step 1: Understand the purpose of AVG()

   The AVG() function is designed to calculate the average (mean) of numeric values in a column.

2. Step 2: Compare with other aggregate functions

   Unlike COUNT(), MAX(), or SUM(), AVG() specifically returns the average value.

3. Final Answer:

   Calculates the average value of a numeric column -> Option A

4. Quick Check:

   AVG() = average calculation [OK]

Hint: AVG() always returns the mean of numbers, not counts or sums [OK]

Common Mistakes:

• Confusing AVG() with COUNT()
• Thinking AVG() sums values without dividing
• Assuming AVG() works on non-numeric columns

2. Which of the following is the correct syntax to find the average salary from a table named Employees?

easy

A. SELECT AVG salary FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT AVERAGE(salary) FROM Employees;

D. SELECT salary AVG() FROM Employees;

Solution

1. Step 1: Recall correct AVG() syntax

   The AVG() function requires parentheses around the column name: AVG(column_name).

2. Step 2: Check each option

   Correct syntax uses AVG(salary) with parentheses. Missing parentheses, AVERAGE(salary), or salary AVG() cause syntax errors.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: AVG() always needs parentheses around the column name [OK]

Common Mistakes:

• Omitting parentheses in AVG()
• Using wrong function name like AVERAGE()
• Placing AVG() after column name incorrectly

3. Given the table Scores with values:
Score
90
80
NULL
70
What will the query SELECT AVG(Score) FROM Scores; return?

medium

A. 240

B. NULL

C. 80

D. 75

Solution

1. Step 1: Identify values considered by AVG()

   AVG() ignores NULL values, so it averages 90, 80, and 70.

2. Step 2: Calculate the average

   (90 + 80 + 70) / 3 = 240 / 3 = 80.

3. Final Answer:

   80 -> Option C

4. Quick Check:

   AVG ignores NULL, average = 80 [OK]

Hint: AVG() skips NULLs automatically when averaging [OK]

Common Mistakes:

• Including NULL as zero in average
• Returning NULL if any NULL exists
• Summing values without dividing

4. Consider this query:
SELECT AVG(price) FROM Products WHERE price > 0;
It returns NULL even though there are products with price 0 and above. What is the likely problem?

medium

A. The WHERE clause excludes all rows because price > 0 filters out zero prices

B. AVG() cannot be used with WHERE clause

C. The price column contains only NULL values

D. AVG() requires GROUP BY to work

Solution

1. Step 1: Analyze the WHERE clause condition

   The condition price > 0 excludes prices equal to zero, so only prices greater than zero are included.

2. Step 2: Consider data and NULL result

   If no prices are greater than zero, the filtered set is empty, so AVG() returns NULL.

3. Final Answer:

   The WHERE clause excludes all rows because price > 0 filters out zero prices -> Option A

4. Quick Check:

   Empty filtered rows cause AVG() to return NULL [OK]

Hint: Check WHERE filters exclude all rows causing NULL AVG() [OK]

Common Mistakes:

• Thinking AVG() can't use WHERE
• Assuming AVG() needs GROUP BY always
• Ignoring that empty sets return NULL

5. You have a table Sales with columns Region and Amount. How do you write a query to find the average sales amount per region, excluding regions with no sales?

hard

A. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount IS NOT NULL;

B. SELECT Region, AVG(Amount) FROM Sales WHERE Amount > 0;

C. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount > 0;

D. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL;

Solution

1. Step 1: Group sales by region

   Use GROUP BY Region to calculate average per region.

2. Step 2: Exclude regions with no sales

   Regions with no sales have AVG(Amount) as NULL, so use HAVING AVG(Amount) IS NOT NULL to filter them out.

3. Final Answer:

   SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL; -> Option D

4. Quick Check:

   GROUP BY + HAVING filters NULL averages [OK]

Hint: Use HAVING to exclude groups with NULL averages [OK]

Common Mistakes:

• Using WHERE after GROUP BY (invalid syntax)
• Not filtering NULL averages with HAVING
• Filtering rows before grouping instead of after