SQL AVG()

Last stop in Module 3. Beacon Robotics' finance team already has the total payroll (SUM) and the headcount (COUNT). AVG() answers the question those two numbers imply but don't directly state: what does the average employee actually earn?

Learning Objectives

• Use AVG() to find the mean of a numeric column.
• Understand how AVG() relates mathematically to SUM() and COUNT().
• Round an AVG() result to a fixed number of decimal places.

AVG() on a Numeric Column

Like SUM(), AVG() requires a numeric column. It returns the mean, the sum of all non-NULL values divided by how many non-NULL values there are, not divided by the total row count.

SELECT AVG(salary) AS average_salary
FROM employees;

How AVG() Relates to SUM() and COUNT()

These two columns return the same value. AVG(salary) is shorthand for SUM(salary) divided by COUNT(salary), specifically COUNT(salary), not COUNT(*), since both SUM() and COUNT(salary) already exclude NULL salaries the same way. If salary were divided by COUNT(*) instead, a NULL salary would quietly pull the average down, which isn't what AVG() does.

This is also why averaging a column with NULLs isn't the same as treating those NULLs as zero: a 100, 200, NULL column averages to 150 (300 / 2), not 100 (300 / 3), exactly as covered in the aggregate functions overview.

SELECT
  AVG(salary) AS using_avg,
  SUM(salary) / COUNT(salary) AS same_result_manually
FROM employees;

Rounding AVG()'s Result

AVG() often returns a result with far more decimal places than useful, 91666.66666... for instance. ROUND(value, decimal_places) trims it, here to 2 decimal places, and works identically across all six databases in this course. ROUND() itself gets a full lesson in a later module, this is just enough to produce a readable result now.

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

Try It Yourself

Write a query that returns the average salary in the Engineering department, rounded to 2 decimal places.

SELECT ROUND(AVG(salary), 2) AS avg_engineering_salary
FROM employees
WHERE department = 'Engineering';

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use AVG() directly rather than manually dividing SUM() by COUNT(*), to avoid the NULL-handling mismatch above.
• Round AVG() results meant for display, raw floating-point averages are rarely the right thing to show a user.
• Remember AVG() returns NULL, not 0, when nothing matches, same as SUM().

Interview Questions

Summary

AVG() returns the mean of a numeric column, equivalent to SUM(column) / COUNT(column), not COUNT(*), which is exactly why it handles NULLs correctly without any extra work. ROUND() cleans up its often-long decimal result for display.

That completes Module 3, Aggregate Functions: what an aggregate function is, MIN() and MAX() (including their text/date behavior), COUNT() (including DISTINCT), SUM() (including its NULL-on-zero-rows quirk), and AVG() (including its relationship to SUM() and COUNT()).

What's Next?

Module 4, Joins, is next, Beacon Robotics' data lives in multiple tables (employees, departments, projects), and joins are how a query pulls matching rows from more than one of them at once, starting with an overview before INNER, LEFT, RIGHT, FULL, and SELF joins.