AVG function in SQL

Introduction

The AVG function helps you find the average value of numbers in a column. It is like finding the middle point of a group of numbers.

To find the average score of students in a class.

To calculate the average price of products in a store.

To see the average temperature over a week.

To find the average salary of employees in a company.

To check the average rating of a product from customer reviews.

Syntax

SQL

SELECT AVG(column_name) FROM table_name;

AVG works only on numeric columns.

It ignores empty or NULL values when calculating the average.

Examples

Finds the average price of all products.

SQL

SELECT AVG(price) FROM products;

Finds the average score for Math exams only.

SQL

SELECT AVG(score) FROM exams WHERE subject = 'Math';

Calculates the average salary of employees in the Sales department.

SQL

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

Sample Program

This creates a sales table, adds some amounts including a NULL, and finds the average amount ignoring the NULL.

SQL

CREATE TABLE sales (
  id INT,
  amount DECIMAL(10,2)
);

INSERT INTO sales (id, amount) VALUES
(1, 100.00),
(2, 150.00),
(3, 200.00),
(4, NULL);

SELECT AVG(amount) AS average_amount FROM sales;

Important Notes

AVG ignores NULL values, so they do not affect the average calculation.

If all values are NULL, AVG returns NULL.

You can use AVG with GROUP BY to find averages for different groups.

Summary

AVG calculates the average of numeric values in a column.

It skips NULL values automatically.

Useful for quick summary statistics like average price, score, or salary.

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