Sample Data
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
We'll use this students table for all examples:
What is AVG?
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
AVG calculates the average. Like finding your average test score.
SELECT AVG(age) FROM students;Result: 21.5 (if ages are 20+22+21+23 = 86, divided by 4)
How AVG Works
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
Basic Syntax
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SELECT AVG(column_name) FROM table_name;Example 1: Using Aliases
Result
| average_age |
|---|
| 21.5 |
1 row
Give your result a nice name:
SELECT AVG(age) AS average_age FROM students;Example 2: AVG with WHERE
Result
| avg_age |
|---|
| 20.5 |
1 row
Calculate average for specific rows:
-- Average age of grade A students only
SELECT AVG(age) AS avg_age FROM students WHERE grade = 'A';Example 3: Combine with COUNT and SUM
Result
| total_students | total_age | average_age |
|---|---|---|
| 4 | 86 | 21.5 |
1 row
Use multiple functions together:
SELECT
COUNT(*) AS total_students,
SUM(age) AS total_age,
AVG(age) AS average_age
FROM students;Try It Below
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
Use the playground to practice:
SELECT AVG(age) FROM students;SELECT AVG(age) AS avg_age FROM students WHERE grade = 'A';
What Comes Next
students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
Next: Learn MIN and MAX to find smallest and largest values.
Try AVG
Calculate average. Try: SELECT AVG(age) AS average_age FROM students;
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...
AVG with WHERE
Average age of grade A students. Try changing the grade!
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...
Combine COUNT, SUM, AVG
Use all three functions together!
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...