Course contentsShow
← Data Science
Lesson 884 of 2,14523. SQL Aggregation and GroupingFree lesson

AVG: Computing Averages

Using AVG to calculate mean values, understanding how nulls are handled, and avoiding common pitfalls.

AVG: Computing Averages

You'll learn how to calculate mean values in SQL using the AVG function and handle common pitfalls like NULL values.

What is AVG?

The AVG() aggregate function computes the arithmetic mean (average) of a numeric column. Think of it like a calculator that adds up all the values in a column and divides by the count of those values—but it does this instantly across thousands or millions of rows.

Basic Syntax

SELECT AVG(column_name)
FROM table_name;

For example, to find the average price of products:

SELECT AVG(price)
FROM products;

How NULL Values Are Handled

Critical point: AVG() automatically ignores NULL values. It only averages the non-NULL rows.

If you have prices of 10, 20, 30, and NULL, the average is (10+20+30)/3 = 20, not (10+20+30+0)/4. This is usually what you want, but be aware—NULLs aren't treated as zeros.

Common Pitfalls

  1. Wrong data type: Applying AVG() to non-numeric columns causes errors
  2. Integer division: Some databases may truncate decimal places if the column is an integer type
  3. Empty result sets: AVG() on zero rows returns NULL, not zero
  4. Assuming NULLs = zeros: They don't! If you need to treat NULLs as zeros, handle them first with other techniques

Combining with Aliases

SELECT AVG(salary) AS average_salary
FROM employees;

This makes your output more readable and professional.

Key Takeaway: AVG() calculates the mean of non-NULL numeric values in a column—remember that NULL values are excluded from both the sum and the count, not treated as zeros.