AVG

AVG: The Arithmetic Mean

AVG(column) computes the average (arithmetic mean) of the non-NULL numeric values in a column: sum of the values divided by the count of non-NULL values (not the total row count).

sql
SELECT AVG(amount) FROM orders;

With amounts 250.00, NULL, 400.00, 150.00, 300.00:

AVG(amount)
------------
     275.00

AVG computes (250 + 400 + 150 + 300) / 4 = 1100 / 4 = 275.00. The denominator is 4 (non-NULL values), not 5 (total rows) and not 6 as if NULL contributed 0 to a sum divided by all rows.

AVG Is Not the Same as SUM / COUNT(*)

A very common mistake is assuming AVG(x) = SUM(x) / COUNT(*). That's only true when there are no NULLs. The correct identity is:

AVG(x) = SUM(x) / COUNT(x)

Verify: SUM(amount) = 1100, COUNT(amount) = 4 → 1100 / 4 = 275. If you (incorrectly) divided by COUNT(*) = 5, you'd get 220, which is wrong.

sql
-- These two produce the SAME correct result:
SELECT AVG(amount) FROM orders;                          -- 275.00
SELECT SUM(amount) / COUNT(amount) FROM orders;           -- 275.00

-- This produces a DIFFERENT (incorrect, if you wanted a true average) result:
SELECT SUM(amount) / COUNT(*) FROM orders;                 -- 220.00

AVG with a Filter

sql
SELECT AVG(amount) FROM orders WHERE status = 'completed';
-- (250 + 400 + 300) / 3 = 950 / 3 = 316.666...

Integer Division Trap

In some dialects, AVG on an integer column still correctly returns a decimal/float result (most engines special-case AVG to avoid the integer-division truncation problem). However, if you manually compute SUM(int_col) / COUNT(int_col) where both are integer types, some dialects (e.g. older MySQL configurations, or explicit integer casts) may perform integer division and truncate the decimal part — this is the same integer-vs-decimal-division nuance discussed for the / operator in Chapter 8 (Data Types / NULL semantics). Casting one operand to a decimal/float avoids it: SUM(amount) / COUNT(amount)::DECIMAL.

Edge Cases

• AVG() over zero rows or all-NULL values returns NULL, not 0 or an error.
• AVG(DISTINCT column) averages only the distinct non-NULL values.
• Averaging a column with extreme outliers behaves exactly like ordinary arithmetic mean — SQL has no built-in median or mode function in the standard aggregate set (some dialects add PERCENTILE_CONT for median-like calculations, which is beyond this chapter's scope).

Key Takeaways / Interview Q&A

Q: What does AVG() divide by — total rows or non-NULL values? A: The count of non-NULL values in that column, i.e. AVG(x) = SUM(x) / COUNT(x), not SUM(x) / COUNT(*).

Q: If a column has 10 rows but 3 are NULL, what's the denominator when computing AVG? A: 7 — only the non-NULL values are averaged.

Q: What does AVG() return over an empty set? A: NULL.

Q: Why might manually writing SUM(col)/COUNT(col) give a wrong, truncated result? A: If both operands are integer types, some dialects perform integer division and truncate the fractional part — cast to decimal/float first.