What You Will Learn
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
In this lesson, you will understand:
- What AVG function does
- How to calculate averages
- Using AVG with WHERE clause
- Rounding average results
- Real-world applications
What is AVG?
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
AVG calculates the average (mean) of numeric values. It adds all values and divides by the count.
Think of it like:
- Calculating your average test score
- Finding average product price
- Computing average customer age
Basic Syntax
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
SELECT AVG(column_name) FROM table_name;Simple Examples
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
Example 1: Average Age
| AVG(age) |
|---|
| 21 |
SELECT AVG(age) FROM students;If ages are: 18, 20, 22, 24 Calculation: (18 + 20 + 22 + 24) / 4 = 21
Example 2: Average with Alias
| average_age |
|---|
| 21 |
SELECT AVG(age) AS average_age FROM students;Makes the result column name more readable.
Example 3: Average with WHERE
| avg_age |
|---|
| 21 |
SELECT AVG(age) AS avg_age
FROM students
WHERE grade = 'A';Calculates average age only for A-grade students.
Rounding Results
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
AVG often returns many decimal places. Use ROUND to clean it up:
SELECT ROUND(AVG(age), 2) AS avg_age FROM students;ROUND(value, 2) rounds to 2 decimal places.
Examples:
- ROUND(21.666666, 2) = 21.67
- ROUND(21.666666, 1) = 21.7
- ROUND(21.666666, 0) = 22
AVG Ignores NULL
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
NULL values are automatically excluded:
Example: If ages are 20, NULL, 22, NULL, 24
- AVG(age) = (20 + 22 + 24) / 3 = 22
- NULL values ignored, not counted as 0
Real-World Examples
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
Example 1: Average Order Value
SELECT ROUND(AVG(total), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';Example 2: Average Salary by Department
SELECT
department,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;Example 3: Average Product Rating
SELECT ROUND(AVG(rating), 1) AS avg_rating
FROM reviews
WHERE product_id = 101;Example 4: Average Response Time
SELECT ROUND(AVG(response_time_ms), 0) AS avg_response
FROM api_logs
WHERE date = CURRENT_DATE;Common Mistakes
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
Mistake 1: Using AVG on Text
Wrong:
SELECT AVG(name) FROM students;
-- Error: Cannot calculate average of textCorrect:
SELECT AVG(age) FROM students;Mistake 2: Using AVG in WHERE
Wrong:
SELECT * FROM products WHERE price > AVG(price);Correct:
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);Mistake 3: Not Rounding
Hard to read:
SELECT AVG(price) FROM products;
-- Result: 125.4567891234Better:
SELECT ROUND(AVG(price), 2) FROM products;
-- Result: 125.46Summary
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
Key points to remember:
- AVG calculates mean of numeric values
- Automatically ignores NULL values
- Returns decimal number (often many digits)
- Use ROUND for cleaner output
- Can combine with WHERE to filter
- Cannot use directly in WHERE clause
- Use with GROUP BY for averages per group
- Only works on numeric columns
- If all values are NULL, returns NULL
- Common for salaries, prices, scores, ages
What Comes Next
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 18 | B |
| 3 | Peter | 22 | A |
| 4 | Sarah | 24 | B |
Now you know how to calculate averages. Next, you will learn about MIN and MAX to find the smallest and largest values.
Calculate average age of all students
Click "Run Query" to see results
Calculate and round average age
Click "Run Query" to see results
Average age of A-grade students only
Click "Run Query" to see results