SQL – AVG

AVG() computes the mean by summing all non-NULL values and dividing by the count of non-NULL rows. The key word is non-NULL — if you have 10 students and 2 have no score (NULL), SQL calculates the average from the 8 existing scores, not divides by 10. Knowing this prevents silent errors in statistical reports.

Use ROUND(AVG(score), 2) to control decimal places — raw averages often produce unwieldy numbers like 83.66666667.

AVG gotcha with NULLs

-- These give different results if score has NULLs:
AVG(score)                        -- divides by non-null count
SUM(score) / COUNT(*)             -- divides by total row count
COALESCE(AVG(score), 0)           -- replace NULL result with 0

Try It Yourself — Interactive SQL Editor

Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.

Key Points

• AVG ignores NULLs — be aware of what that means for your data
• Use ROUND() to make averages readable
• Weighted averages require SUM(value * weight) / SUM(weight)
• AVG is only meaningful on numeric columns

Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.

In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.