VMware ESXi and vSphere Cluster Management

SQL AVG() Function: Calculate Average Values

Learn how to use SQL AVG() to calculate averages, handle NULL values, filter rows, group results, use HAVING, round output, and compare distinct averages.

AVG() is an SQL aggregate function that calculates the arithmetic mean of numeric values. It is useful for finding average prices, scores, quantities, measurements, and other numbers stored in one or more rows.

An aggregate function processes values from multiple rows and returns one result for the entire query or one result for each group. The arithmetic mean is calculated conceptually as:

sum of eligible values / number of eligible values

For example, the average of 10, 20, and 30 is 20. AVG() normally excludes NULL values from both the total and the count.

Basic AVG() syntax

SELECT AVG(column_name) AS average_value
FROM table_name;

• AVG(column_name) is the numeric expression being averaged.
• FROM table_name identifies the source table.
• AS average_value assigns a readable alias to the returned column.

An alias is a temporary output name. Without an alias, a database may label the result with an expression such as AVG(buyPrice), which is less convenient to read.

AVG() syntax reference

Calculate a simple average

Suppose a products table has a numeric buyPrice column. This query calculates one average across all eligible products:

SELECT AVG(buyPrice) AS avg_price
FROM products;

Because there is no GROUP BY, the query returns one aggregate row.

The exact value and number of displayed decimal places depend on the database system and the input data type.

Sample data and NULL handling

For these rows, AVG(buyPrice) is calculated as (20 + 30 + 50) / 3, which equals 33.333333.... The NULL price is not treated as zero. It contributes neither to the sum nor to the count.

How AVG() handles NULL values

NULL represents a missing or unknown value. AVG() ignores NULL values in the expression it receives.

• A row with a non-NULL numeric value contributes to the total and the count.
• A row whose averaged expression is NULL contributes to neither.
• If no eligible non-NULL values exist, AVG() returns NULL.

This behavior aligns with COUNT(column_name), which counts non-NULL values rather than every row. To inspect the number of rows and the number of values available to AVG(), use:

SELECT COUNT(*) AS matching_rows,
       COUNT(reading) AS non_null_readings,
       AVG(reading) AS average_reading
FROM measurements;

COUNT(*) counts matching rows, while COUNT(reading) counts only rows with a non-NULL reading.

Filter rows before averaging with WHERE

Use WHERE when the average should include only rows satisfying a row-level condition. For example, this query averages prices in one product category:

SELECT AVG(buyPrice) AS avg_price
FROM products
WHERE productLine = 'Classic Cars';

Only products whose productLine is Classic Cars contribute to the result. You can also filter by dates, status values, regions, or numeric ranges:

SELECT AVG(amount) AS average_amount
FROM payments
WHERE payment_date >= '2026-01-01'
  AND payment_date <  '2026-02-01';

WHERE filters individual input rows before aggregation occurs. It does not filter an already calculated group average.

Calculate averages by group

Use GROUP BY to partition rows into groups and calculate one average per group. Include the grouping column in the SELECT list so each result can be identified:

SELECT productLine,
       AVG(buyPrice) AS avg_price
FROM products
GROUP BY productLine
ORDER BY avg_price DESC;

The result contains one row for each product line, ordered from the highest average price to the lowest.

Every group applies the same NULL rules: NULL values do not contribute to that group's average.

Filter grouped averages with HAVING

Use HAVING when the condition depends on an aggregate result calculated for each group:

SELECT productLine,
       AVG(buyPrice) AS avg_price
FROM products
GROUP BY productLine
HAVING AVG(buyPrice) > 50
ORDER BY avg_price DESC;

This keeps only product lines whose calculated average exceeds 50.

An aggregate condition such as AVG(buyPrice) > 50 generally belongs in HAVING, not WHERE. WHERE can still be used alongside HAVING to reduce the input rows first:

SELECT productLine,
       AVG(buyPrice) AS avg_price
FROM products
WHERE discontinued = 0
GROUP BY productLine
HAVING AVG(buyPrice) > 50;

Average numeric expressions

AVG() can receive a numeric expression, not only a bare column. For example:

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

The expression is evaluated for each eligible row, and AVG() calculates the mean of those results. If the expression produces NULL for a row, that row is excluded from the calculation.

Another example applies a conversion before averaging:

SELECT AVG(temperature_celsius * 9.0 / 5.0 + 32) AS average_temperature_fahrenheit
FROM measurements;

Result precision and rounding

The returned data type, decimal scale, and displayed precision vary between database systems and can depend on the input column's type. An output such as 54.67333333333333 is not automatically incorrect; it may simply show the database's precision for the calculation.

For presentation, use a database-supported rounding function such as ROUND():

SELECT ROUND(AVG(buyPrice), 2) AS avg_price
FROM products;

This returns the average rounded to two decimal places. Keep in mind that rounding changes the displayed result. If precision matters for later calculations, retain the unrounded value and round only in the reporting layer or final output.

Distinct averages

AVG(DISTINCT column_name) removes duplicate non-NULL values before calculating the mean:

SELECT AVG(score) AS all_scores_average,
       AVG(DISTINCT score) AS unique_scores_average
FROM scores;

Suppose the scores are 10, 10, and 20:

• AVG(score) is (10 + 10 + 20) / 3 = 13.3333....
• AVG(DISTINCT score) is (10 + 20) / 2 = 15.

Ordinary AVG() gives each row equal weight, so repeated values influence the result according to how many rows contain them. DISTINCT treats each unique value once. Use DISTINCT only when that matches the business question, such as finding the average of unique price points. It is inappropriate when every row represents a separate observation that should be weighted equally.

AVG() compared with other aggregate functions

Conceptually, AVG(column) is closely related to SUM(column) / COUNT(column), because both SUM() and COUNT(column) ignore NULL values:

SELECT AVG(buyPrice) AS avg_price,
       SUM(buyPrice) / COUNT(buyPrice) AS equivalent_mean
FROM products;

The exact result type and division behavior can differ by database system, so AVG() is usually the clearer and safer expression for an average.

Troubleshooting AVG() queries

The average is NULL

This usually means that no rows match the filter or every eligible value is NULL. Compare the total matching rows with the non-NULL count:

SELECT COUNT(*) AS matching_rows,
       COUNT(price) AS non_null_prices,
       AVG(price) AS average_price
FROM products
WHERE productLine = 'Classic Cars';

Review the WHERE condition and inspect the source data.

The average is unexpectedly high or low

• Rows that should have been excluded may still be included.
• A join may duplicate source rows before AVG() runs.
• The query may use the wrong numeric column or measurement unit.
• Outlier values may strongly affect the arithmetic mean.

Inspect the underlying rows using the same WHERE and JOIN conditions. Also compare AVG() with MIN() and MAX() to identify unusual values:

SELECT AVG(price) AS average_price,
       MIN(price) AS minimum_price,
       MAX(price) AS maximum_price
FROM products;

AVG() cannot be applied to the column

The selected column may be stored as text or may contain values that are not reliably numeric. Prefer a genuinely numeric column. If conversion is necessary, clean and validate the source values first, then use the database system's appropriate CAST or conversion syntax.

A condition using AVG() fails in WHERE

WHERE runs before aggregate results are calculated. Move an aggregate condition to HAVING:

SELECT category,
       AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

The average displays too many decimal places

Use ROUND() for a presentation value:

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

Do not assume that a long decimal indicates an error. Check the input values and the database's numeric result type before changing the calculation.

AVG(DISTINCT ...) has an unexpected result

Removing duplicates changes the weighting of values. Use ordinary AVG() when each row is an observation. Use AVG(DISTINCT ...) only when repeated numeric values should count once according to the intended business definition.

Exam-relevant notes

• AVG() returns the arithmetic mean of non-NULL numeric values.
• AVG() ignores NULL; it does not interpret NULL as zero.
• Without GROUP BY, an aggregate query normally returns one result row.
• WHERE filters rows before aggregation.
• GROUP BY creates one aggregate result per group.
• HAVING filters groups after aggregate calculations.
• AVG(DISTINCT value) averages unique non-NULL values and can have a different meaning from AVG(value).
• Use an alias such as AS avg_price to make output readable.
• Use ROUND() when a report needs a fixed number of decimal places, but distinguish display rounding from the underlying calculation.

Summary

Use AVG() to calculate the mean of numeric values across rows. Add WHERE to limit the input rows, GROUP BY to calculate separate averages, and HAVING to filter those grouped averages. Remember that NULL values are excluded, repeated rows normally retain their weight, and the returned precision depends on the database system and input type.

For related aggregate concepts, review the SQL AVG() function reference.