Calculates the arithmetic mean of values from many rows.
AVG(<expression>)
An aggregate function: it adds the values up and divides by their count. NULL counts neither in the sum nor in the count. The result usually has many decimal places, so it's often wrapped in ROUND.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| average_salary |
|---|
| 1140.0000000000000000 |
| department | average_salary |
|---|---|
| backend | 1100 |
| design | 1200 |
| frontend | 1150 |
| skip_nulls | nulls_as_zero |
|---|---|
| 216.67 | 130.00 |
The first divides the sum of bonuses by 3 — only those who have one. The second counts a missing bonus as 0 and divides by 5.
NULL isn't counted as zero: the average of 300, NULL and 150 is 225, not 150. If a missing value means zero, write AVG(COALESCE(bonus, 0)).
For integers the result is still fractional, with a pile of zeros: AVG(salary) gives 1140.0000000000000000. Round it: ROUND(AVG(salary), 2).
The average is sensitive to outliers: a single order of 10,000 makes the “average order value” unrealistic. The median shows a typical value better: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount).