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
- Wrong data type: Applying
AVG()to non-numeric columns causes errors - Integer division: Some databases may truncate decimal places if the column is an integer type
- Empty result sets:
AVG()on zero rows returns NULL, not zero - 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.