SQL AVG Function: Examples, NULL Values, and GROUP BY

The SQL AVG function calculates the arithmetic mean of a numeric expression over the selected rows. It adds the non-NULL values and divides by their count. For example, the average of 10, 20, and NULL is 15, not 10: the missing value contributes neither a number nor a place in the denominator.

Use AVG for questions such as average sales per title, average salary per department, or average quantity per order line. Before writing the query, identify what one row represents and which observations belong in the calculation. A correct function call can still answer the wrong business question if rows are duplicated by a join or missing values are treated incorrectly.

The examples use ordinary numeric SQL. Runnable inline datasets are labeled SQL Server 2025 or Oracle 26ai where their syntax differs; table-based examples assume the named tables and columns exist.

AVG(expression)
AVG(DISTINCT expression)

The expression can be a numeric column or a calculation such as price * quantity. By default, repeated values count once for every row in which they occur. DISTINCT instead averages the different numeric values; it does not identify different customers or remove duplicate business records.

AVG(*) is not valid. Specify the value to average. Conceptually, AVG is a sum divided by a non-NULL count, but a hand-written SUM(x) / COUNT(x) also requires care with division by zero and numeric types.

This SQL Server 2025 query supplies its own data and does not create or modify a table:

SELECT AVG(CAST(v.score AS decimal(10,2))) AS average_score,
       COUNT(v.score) AS known_scores,
       COUNT(*) AS total_rows
FROM (VALUES (10), (20), (NULL)) AS v(score);
-- Expected numeric values: 15, 2, 3

COUNT(score) counts the two known scores, while COUNT(*) counts all three rows. Displaying both counts helps readers understand how much evidence supports the average. The decimal cast makes fractional averages possible in SQL Server even when the source values are integers.

SQL Server and Oracle numeric AVG ignore NULL inputs. A stored zero is a known value and is included. Replacing NULL with zero therefore changes the meaning of the report:

-- SQL Server 2025: compare two different assumptions.
SELECT AVG(CAST(v.score AS decimal(10,2))) AS known_score_average,
       AVG(COALESCE(CAST(v.score AS decimal(10,2)), 0))
           AS missing_counted_as_zero
FROM (VALUES (10), (20), (NULL)) AS v(score);
-- Expected numeric values: 15 and 10

The second calculation is appropriate only when the business rule says that a missing score should count as zero. It is not a general repair for incomplete data. For an exam that has not yet been marked, for example, zero and unknown represent different facts.

Without GROUP BY, AVG returns one row containing NULL when no rows qualify or when every qualifying value is NULL. With GROUP BY, an existing group containing only NULL values has a NULL average; if no rows qualify, there are no groups and no result rows.

COALESCE(AVG(score), 0) replaces a missing final result with zero. It does not insert zero for each missing input, so it differs from AVG(COALESCE(score, 0)).

In SQL Server 2025, AVG over an int expression returns int; AVG over bigint returns bigint. Fractional information can therefore be lost. Casting only the final result cannot restore it:

SELECT AVG(v.qty) AS integer_average,
       AVG(CAST(v.qty AS decimal(18,2))) AS decimal_average,
       CAST(AVG(v.qty) AS decimal(18,2)) AS cast_too_late
FROM (VALUES (1), (2)) AS v(qty);
-- Expected numeric values: 1, 1.5, 1

Choose a decimal precision and scale that fit the source data; casting to two decimal places can itself round inputs with more fractional digits. SQL Server returns decimal(38, max(s,6)) for AVG of a decimal(p,s) expression. Approximate types such as float remain approximate, and very large accumulated values can overflow.

SELECT ROUND(AVG(CAST(qty AS decimal(18,2))), 2) AS average_qty
FROM OrderLines;

ROUND changes the numeric value, but it does not guarantee that a client displays exactly two decimal places. Apply presentation formatting separately when a report requires it.

SELECT DepartmentId,
       AVG(CAST(Salary AS decimal(18,2))) AS average_salary,
       COUNT(Salary) AS salaries_in_average
FROM Employees
GROUP BY DepartmentId
ORDER BY DepartmentId;

Each department produces one summary row. The denominator is the number of non-NULL salaries in that department, not the headcount across the organization. DepartmentId belongs in GROUP BY because it is selected alongside an aggregate and defines the groups.

Suppose department A has salaries of 40,000 and 60,000, while department B has one salary of 90,000. Their departmental averages are 50,000 and 90,000. The company average is approximately 63,333.33, not the unweighted average of those two departmental averages, 70,000. Groups with different counts need different weights when combined.

To list individual titles whose sales exceed the catalog average, calculate the comparison value in a scalar subquery:

SELECT title_id, sales
FROM titles
WHERE sales > (
    SELECT AVG(CAST(sales AS decimal(18,2)))
    FROM titles
)
ORDER BY sales DESC;

The outer query retains individual rows; the inner query produces one average. NULL sales do not contribute to that average and do not satisfy the comparison. If all sales are NULL, the comparison produces no qualifying rows. If the business question is “above the average for business titles,” apply that category condition to the relevant outer and inner queries rather than unintentionally using the whole catalog.

-- SQL Server 2025
SELECT AVG(CAST(v.amount AS decimal(10,2))) AS average_per_row,
       AVG(DISTINCT CAST(v.amount AS decimal(10,2)))
           AS average_distinct_amount
FROM (VALUES (10), (10), (40)) AS v(amount);
-- Expected numeric values: 20 and 25

The ordinary average is (10 + 10 + 40) / 3. DISTINCT changes the input to 10 and 40, giving 25. If two separate sales both have an amount of 10, both normally belong in an average sale amount. DISTINCT would discard meaningful frequency information.

Similarly, do not use DISTINCT to hide a faulty join. Joining an order to its lines repeats the order-level amount once per matching line. Averaging those repeated amounts weights orders with more lines more heavily. Calculate at the intended grain, such as one row per order, before aggregating.

• AVG uses non-NULL values; zero is included and unknown is excluded.
• Cast the input before AVG when SQL Server integer inputs must produce a fractional mean.
• Use GROUP BY for separate averages and HAVING for aggregate conditions.
• Check the row grain after joins and preserve repeated values when they represent separate observations.
• Use a weighted sum divided by eligible total weight when each row should not count equally.

Compare averages with the SUM function, then continue to MIN and MAX to identify the smallest and largest values.