SQL online course

SQL AVG() Function: Calculate Average Values

Learn how SQL AVG() calculates arithmetic means, handles NULL values, filters and groups rows, uses HAVING, expressions, DISTINCT, joins, subqueries, and window functions.

The SQL AVG() function calculates the arithmetic mean of numeric values. It is an aggregate function: a function that summarizes values from multiple rows into one result, either for the whole result set or for each group.

Typical uses include calculating average prices, test scores, quantities, durations, ratings, and salaries. Unlike COUNT(), which counts rows or values, and SUM(), which totals values, AVG() returns a mean.

Basic AVG() syntax

SELECT AVG(numeric_expression) AS average_value
FROM table_name;

The argument is usually a numeric column, but it can also be a numeric expression such as unit_price * quantity. An alias gives the calculated column a readable name.

SELECT AVG(price) AS average_price
FROM products;

This query returns one value: the average of the eligible values in products.price.

How AVG() calculates a result

Conceptually, the arithmetic mean is:

sum of eligible numeric values / number of eligible numeric values

For example, if the values are 10, 20, and 30, the average is 60 divided by 3, or 20. AVG() ignores NULL. A NULL value means missing or unknown data; it is not treated as zero.

For example, suppose a test_scores table contains these rows:

AVG(score) over all rows is 80 because the calculation is (80 + 90 + 70) / 3. The missing score is not a zero and is not included in the denominator.

Filtering rows with WHERE

WHERE filters individual rows before the average is calculated. Use it for conditions involving dates, categories, statuses, or thresholds.

SELECT AVG(score) AS average_active_score
FROM test_scores
WHERE status = 'active';

Only active students are considered. The result is 85 because the non-NULL active scores are 80 and 90.

SELECT AVG(price) AS average_recent_price
FROM products
WHERE created_at >= '2026-01-01'
  AND category = 'Books';

A WHERE condition cannot normally refer to an aggregate result from the same query level. Use HAVING for grouped aggregate results.

Calculating averages with GROUP BY

GROUP BY divides rows into groups and calculates one average per group.

SELECT department,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This returns one row for each department. In standard SQL, every selected column that is not inside an aggregate function must be included in GROUP BY.

SELECT region,
       product_category,
       AVG(price) AS average_price
FROM products
GROUP BY region, product_category;

Here, each region-and-category combination gets its own average.

Filtering grouped averages with HAVING

HAVING filters groups after aggregation. It is appropriate when the condition depends on AVG(), SUM(), or another aggregate.

SELECT department,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;

You can use both clauses: WHERE first restricts the source rows, then GROUP BY calculates group averages, and HAVING removes groups that do not meet the aggregate condition.

Aliases and readable results

An alias is a temporary descriptive name for a selected expression. Use names such as average_price, avg_score, or mean_salary.

SELECT AVG(score) AS avg_score
FROM test_scores;

Aliases improve reports and make application code easier to understand. See SQL aliases for more alias syntax.

AVG() with expressions

You can average a calculated numeric value instead of a stored column.

SELECT AVG(unit_price * quantity) AS average_line_value
FROM order_items;

This averages the value of each line item. You can also calculate a discount-adjusted amount:

SELECT AVG(unit_price * quantity * (1 - discount_rate))
       AS average_discounted_value
FROM order_items;

Check the units before interpreting the result. An average line-item value is not necessarily an average order value. If any input to an expression is NULL, the expression may become NULL and that row will be excluded from AVG().

NULL handling and COALESCE

Normally, excluding a missing value is the correct behavior:

SELECT AVG(score) AS average_score
FROM test_scores;

If the business rule explicitly says that a missing score represents zero, replace NULL with zero before averaging:

SELECT AVG(COALESCE(score, 0)) AS average_with_missing_as_zero
FROM test_scores;

These queries answer different questions. The first averages scores that exist. The second treats every missing score as a zero, which can substantially lower the result. Do not use COALESCE without confirming that this meaning is intended.

Data types, precision, and rounding

AVG() requires a numeric expression, or a value that the database system can convert to a numeric type. Return-type rules differ among database products and can depend on whether the input is an integer, decimal, or floating-point type.

For reporting, round the result when appropriate:

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Rounding changes the displayed result, not necessarily the stored calculation. If exact precision matters, cast the expression or result to an appropriate decimal type using the syntax supported by your database.

SELECT CAST(AVG(price) AS DECIMAL(12, 2)) AS average_price
FROM products;

Do not store formatted strings such as '$19.99' as the input to numeric calculations. Store numeric values in numeric columns and format them only when presenting results.

Duplicates and AVG(DISTINCT ...)

Regular AVG(column) uses every qualifying row, even when values repeat.

SELECT AVG(rating) AS average_rating
FROM reviews;

If the ratings are 2, 2, and 5, the ordinary average is 3. If your database supports it, AVG(DISTINCT rating) averages unique input values instead:

SELECT AVG(DISTINCT rating) AS unique_rating_average
FROM reviews;

The unique-value average for 2, 2, and 5 is 3.5 because it calculates (2 + 5) / 2. DISTINCT changes the question; use it only when repeated values should have no additional influence.

AVG() in joins

A join can multiply rows. For example, joining one order to several order-item rows produces several joined rows for that order. Averaging a value from the order table after that join may count the same order multiple times.

When the intended observation is one row per order, aggregate at that grain before joining:

WITH order_totals AS (
    SELECT order_id,
           SUM(unit_price * quantity) AS order_total
    FROM order_items
    GROUP BY order_id
)
SELECT AVG(order_total) AS average_order_value
FROM order_totals;

This first creates one total per order and then averages those order totals. The correct approach depends on the business question: average line-item value and average order value are different measures.

AVG() in subqueries

A scalar subquery can calculate an overall average for comparison with each row.

SELECT employee_id,
       employee_name,
       salary,
       salary - (SELECT AVG(salary) FROM employees) AS difference_from_overall
FROM employees;

A correlated subquery can compare an employee with the average for that employee's department, although a window function is often clearer and more efficient:

SELECT e.employee_id,
       e.department,
       e.salary,
       (
         SELECT AVG(e2.salary)
         FROM employees AS e2
         WHERE e2.department = e.department
       ) AS department_average
FROM employees AS e;

Windowed averages

A window function uses an OVER clause to calculate across related rows without collapsing the result set. An aggregate query with GROUP BY returns one row per group; a windowed average keeps every source row.

SELECT employee_id,
       employee_name,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;

Every employee row remains visible, with the average salary for that employee's department added beside it.

An overall average can be added to every row with an empty window:

SELECT employee_id,
       salary,
       AVG(salary) OVER () AS overall_average
FROM employees;

Ordered windows can produce running averages, and a bounded frame can produce moving averages where supported by the database:

SELECT sale_date,
       sale_amount,
       AVG(sale_amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS seven_row_moving_average
FROM daily_sales;

Weighted averages

An ordinary average gives each observation equal influence. A weighted average accounts for a quantity, count, or other weight.

SELECT SUM(value * weight) / NULLIF(SUM(weight), 0) AS weighted_average
FROM measurements;

NULLIF prevents division by zero in systems that support it. For example, to calculate an average price across products with different quantities, use quantity as the weight rather than averaging product prices equally.

A common mistake is averaging group averages:

-- Potentially misleading when days have different observation counts
SELECT AVG(daily_average)
FROM daily_summary;

If one day has 2 observations and another has 2,000, their daily averages should not automatically receive equal weight. Prefer calculating AVG() from the original detail rows, or use totals and counts to form a weighted average.

Common average patterns

Common mistakes and interpretation

• Expecting NULL to count as zero: AVG(column) excludes NULL values. Use COALESCE only when zero is the defined business meaning.
• Ignoring duplicates: repeated values represent repeated qualifying rows. Use DISTINCT only when unique values, rather than observations, should be averaged.
• Mixing units: do not average seconds and minutes together, or dollars and cents, without conversion.
• Using incomparable observations: confirm that the rows represent the same kind of measurement and time period.
• Overlooking outliers: an outlier, or unusually high or low value, can strongly affect an arithmetic mean. Consider a median or percentile when the data is skewed and your database supports those functions.
• Averaging averages: use a weighted average when groups have different sizes.

Troubleshooting AVG() queries

Checking AVG() with COUNT() and SUM()

When diagnosing a result, inspect the inputs directly:

SELECT COUNT(*) AS row_count,
       COUNT(score) AS non_null_score_count,
       SUM(score) AS score_total,
       AVG(score) AS average_score
FROM test_scores
WHERE status = 'active';

COUNT(*) counts rows, while COUNT(score) counts non-NULL scores. Comparing them reveals whether missing values are being excluded from the denominator.

Quick reference

-- Basic average
SELECT AVG(price) AS average_price
FROM products;

-- Filter before averaging
SELECT AVG(score) AS avg_score
FROM test_scores
WHERE status = 'active';

-- Average per group
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

-- Filter groups by their average
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;

-- Average unique values
SELECT AVG(DISTINCT rating) AS unique_rating_average
FROM reviews;

-- Rounded average
SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

-- Group average while retaining detail rows
SELECT employee_id,
       department,
       AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;

For related aggregate operations, see SQL COUNT(), SQL SUM(), and SQL MIN(). For filtering syntax, see the SQL WHERE clause.