AVG Function
Compute the mean — and understand how NULLs change the result
Intermediate 10 minAVGaggregateaverage
Calculating Averages
AVG computes the arithmetic mean of a numeric column: it sums all non-NULL values and divides by the count of those values. Here's what to watch out for:
NULL Ignorance — AVG skips NULLs entirely. Example: [100, 200, NULL] returns 150 (sum of 300 / count of 2), not 100. NULLs are excluded from both the numerator and the denominator of the calculation.
Precision Control — Use
ROUND(AVG(column), n) to round off lengthy decimal results. Without rounding, AVG can return values like 177.71930000 which are hard to read and unnecessary for most reporting.NULLs as Zeros — Treat NULLs as zeros with
AVG(COALESCE(column, 0)). This ensures that NULLs are replaced with 0 before calculating the average, which changes both the sum and the count in the result.Knowledge Canvas
How AVG Works
Calculate the mean of numeric values
- AVG = SUM(col) / COUNT(col) — skips NULLs in both
- Integer division trap: AVG of integers may truncate
- Use AVG(col * 1.0) or CAST to force decimal results
- AVG(DISTINCT col) averages unique values only
NULL Behavior
NULLs silently change your average
- AVG(10, NULL, 20) = 15 (not 10!) — denominator is 2, not 3
- NULLs reduce the denominator — average may be higher than expected
- Use COALESCE(col, 0) if NULLs should count as zero
- All NULLs → AVG returns NULL
AVG Traps
Subtle mistakes with averages
- Integer AVG:
AVG(3, 4)might return 3 not 3.5 - Fix:
AVG(col * 1.0)orCAST(col AS REAL) - AVG after JOIN → inflated if rows duplicate
- Weighted average needs:
SUM(val*weight)/SUM(weight)
Result Interpretation
Understanding AVG output
- Returns REAL/FLOAT type
- NULL if all values are NULL
- Precision depends on database type handling
- ROUND(AVG(col), 2) for clean display
Syntax
Syntax Template
1-- Plain average (NULLs are skipped)
2SELECT
3 AVG(column)
4FROM table;
5
6-- Rounded for cleaner output
7SELECT
8 ROUND(AVG(column), 2)
9FROM table;
10
11-- Treat NULLs as zero before averaging
12SELECT
13 AVG(COALESCE(column, 0))
14FROM table;
AVG(column)Mean of non-NULL valuesROUND(AVG(...), n)Round to n decimal placesAVG(COALESCE(col, 0))Treats NULLs as 0 in the average Sample Data
reviews
12 rows
| id | product_id | rating |
|---|---|---|
| 7001 | P101 | 5 |
| 7002 | P101 | 4 |
| 7003 | P101 | 3 |
| 7004 | P102 | 5 |
| 7005 | P102 | 4 |
| 7006 | P103 | 4 |
| 7007 | P104 | 5 |
| 7008 | P105 | 5 |
| 7009 | P105 | 4 |
| 7010 | P107 | 3 |
| 7011 | P108 | 5 |
| 7012 | P109 | 5 |
orders
12 rows
| id | order_date | total |
|---|---|---|
| 5001 | 2024-01-15 | 59.98 |
| 5002 | 2024-02-08 | 449 |
| 5003 | 2024-02-20 | 12.5 |
| 5004 | 2024-03-05 | 89.99 |
| 5005 | 2024-03-15 | 245 |
| 5006 | 2024-03-22 | 159 |
| 5007 | 2024-04-02 | 45.5 |
| 5008 | 2024-04-12 | 79 |
| 5009 | 2024-04-22 | 425 |
| 5010 | 2024-05-05 | 18.99 |
| 5011 | 2024-05-18 | 134 |
| 5012 | 2024-06-08 | 65.5 |
Worked Example
Find the average product rating across all reviews.
SQL
1SELECT
2 ROUND(AVG(rating), 2) AS avg_rating,
3 COUNT(*) AS review_count
4FROM reviews;
AVG sums all rating values and divides by the number of non-NULL ratings. ROUND limits the output to 2 decimal places. Showing COUNT alongside AVG is good practice — an average of 5.0 from 2 reviews means something very different than 5.0 from 500 reviews.
Output
1 row
| avg_rating | review_count |
|---|---|
| 4.33 | 12 |
Key Concepts
1AVG([100, 200, NULL]) = 150, not 100 — NULLs are excluded from the count
2Pair AVG with COUNT to give context: avg of 2 vs avg of 2000
3ROUND(AVG(col), 2) prevents long decimal output
4AVG(COALESCE(col, 0)) treats NULLs as zeros before averaging
Pro Tip
Misunderstand NULL behavior in AVG and your dashboard numbers will be silently wrong.
When to Use
Average order value, mean product rating, average response time, price per category.
Find the average order total from the orders table. Round to 2 decimal places. Name it avg_order_value.
Your Query