AVG Function
SQL · Aggregate Functions
Theory
AVG returns the arithmetic mean of a numeric column: the sum of the non-NULL values divided by the number of non-NULL values. With GROUP BY you get one average per group, such as the average salary per department.
The NULL rule matters a lot here. If one IT employee has salary NULL, AVG(salary) for IT divides by 1, not 2, so the NULL row is simply not part of the average. If you want missing values treated as zero, write AVG(COALESCE(salary, 0)). Another gotcha is integer division: in some databases (SQL Server, for example) the average of integer values may come back as an integer, so 7 and 8 give 7 instead of 7.5. Casting to a decimal first, as in AVG(CAST(salary AS DECIMAL(10,2))), avoids it. Use ROUND() to make the result readable.
Why we use it
The mean is the quickest way to describe 'typical' size: average order value, average delivery time, average salary per team. AVG computes it inside the database in a single pass and handles NULLs for you.
When to use it
Use AVG for numbers where the mean makes sense. When data is skewed by a few huge values (like salaries with one CEO), the median is often a better 'typical' value. Decide up front whether missing values should be ignored or counted as 0.
How it works
- Rows arrive after FROM and WHERE
- GROUP BY splits rows into groups
- Is the next value NULL? Yes: Ignore it completely. No: Add it to the sum and 1 to the count.
- Divide sum by count of non-NULL values
- Was the count 0? Yes: Return NULL. No: Return sum / count.
- One average per group
Syntax
AVG(column)Example
SELECT AVG(salary) FROM employees;Key points
- Ignores NULL values in both the sum and the count
- AVG(COALESCE(col, 0)) treats missing values as zero
- Integer columns can give integer averages in some dialects: cast first
- Combine with GROUP BY for per-group averages
- Wrap in ROUND(AVG(col), 2) for readable output