MySQL AVG() Function

MySQL AVG() Function: Calculating Mean Values

The MySQL AVG() aggregate function calculates the arithmetic mean of all non-NULL numeric values in a column or expression. It divides the sum of non-NULL entries by the count of non-NULL entries.

Basic Syntax

SELECT AVG(column_name) AS average_value
FROM table_name
WHERE condition;

Like SUM(), AVG() returns NULL when no rows match or when every value in the column is NULL.

Practical SQL Examples

This query computes the average product price per category in demo_products and formats it to two decimal places with ROUND():

SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM demo_products
WHERE is_active = 1
GROUP BY category
ORDER BY avg_price DESC;

You can also compare individual rows against the table average using a scalar subquery:

SELECT id, product_name, price
FROM demo_products
WHERE price > (SELECT AVG(price) FROM demo_products);

Common Mistakes and Best Practices

• Understand NULL Exclusion in Averages: AVG(rating) divides by the number of rated rows, ignoring unrated NULL rows. If unrated rows should count as zero in your business rule, write AVG(COALESCE(rating, 0)).
• Format Display Precision with ROUND(): AVG() on DECIMAL(10, 2) returns extra decimal digits (e.g., 92.357143), so wrap it in ROUND(AVG(price), 2) for clean financial summaries.
• Avoid Averaging Pre-Aggregated Averages: Computing the average of daily averages skews results when daily order counts differ; always compute SUM(total) / COUNT(*) from raw rows.

Frequently Asked Questions

Does AVG() treat NULL as zero?

No, AVG() ignores NULL rows completely in both the numerator and denominator unless you explicitly wrap the column in COALESCE(col, 0).

Can I use AVG(DISTINCT price)?

Yes, AVG(DISTINCT price) averages each unique price once, discarding duplicate values before dividing.

How do I find rows above the average value?

Put (SELECT AVG(col) FROM table_name) in a WHERE col > ... filter or use a window function AVG(col) OVER ().