SQL AVG

The AVG() function returns the average value of a numeric column. It is useful for prices, scores, ages, quantities and any other number where the center of the data matters.

Syntax

SELECT AVG(column_name)
FROM table_name;

AVG() ignores NULL values. That is usually what you want, but it can surprise you if missing values mean zero in your business rule.

Example

Get the average price of all products:

SELECT AVG(price) AS average_price
FROM products;

AVG with GROUP BY

Average values become more useful when you group rows by a category:

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

This returns one average for each category. Without GROUP BY, the query returns one average for the whole table.

Common mistakes

• Using AVG() on text columns. The column should contain numeric values.
• Forgetting that NULL values are ignored.
• Selecting non-aggregated columns without adding them to GROUP BY.

Related topics

Continue with SQL COUNT, SQL SUM, SQL GROUP BY and the SQL Formatter.