AVG function in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AVG function do in SQL?

The AVG function calculates the average (mean) value of a numeric column in a table.

beginner

How do you use the AVG function to find the average salary from an Employees table?

Use:
SELECT AVG(salary) FROM Employees;
This returns the average salary of all employees.

beginner

Can the AVG function be used with non-numeric columns?

No, AVG only works with numeric columns like integers or decimals. Using it on text or dates will cause an error.

intermediate

What happens if the AVG function is used on a column with NULL values?

NULL values are ignored by AVG. It calculates the average only from the non-NULL values.

intermediate

How can you find the average salary per department using AVG?

Use GROUP BY with AVG:
SELECT department, AVG(salary) FROM Employees GROUP BY department;
This shows average salary for each department.

What does the SQL AVG function calculate?

A The total sum of a numeric column

B The average value of a numeric column

C The maximum value in a column

D The number of rows in a table

AVG calculates the average (mean) of numeric values in a column.

Which SQL query correctly finds the average price from a Products table?

A SELECT AVG(price) FROM Products;

B SELECT SUM(price) FROM Products;

C SELECT COUNT(price) FROM Products;

D SELECT MAX(price) FROM Products;

AVG(price) returns the average price; SUM, COUNT, MAX do different calculations.

How does AVG handle NULL values in a column?

A It counts NULL as one

B It treats NULL as zero

C It ignores NULL values and averages only non-NULL values

D It returns NULL if any NULL exists

AVG skips NULLs and calculates average from existing numeric values.

Can AVG be used on a text column?

A No, AVG only works with numeric columns

B Yes, AVG converts text to numbers automatically

C Yes, AVG counts the number of text entries

D No, AVG only works with date columns

AVG requires numeric data; text columns cause errors.

How do you find the average salary per department?

A SELECT department, SUM(salary) FROM Employees;

B SELECT AVG(salary) FROM Employees;

C SELECT department FROM Employees WHERE AVG(salary);

D SELECT department, AVG(salary) FROM Employees GROUP BY department;

GROUP BY groups rows by department, AVG calculates average salary per group.

Explain how the AVG function works in SQL and how it treats NULL values.

Think about how average is calculated in math and what happens if some values are missing.

Describe how to use the AVG function with GROUP BY to find averages per category.

Grouping data helps calculate averages for each group separately.

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