00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL avg() Function

Last updated:

AVG() calculates the arithmetic mean: the sum of the values divided by how many there are. Average rating, average order value, average delivery time: it's one of the most used aggregate functions, and the one where NULLs most often surprise people.

1. Basic AVG

Table: ratings (9 rows)

idproductreviewerstarshelpful_votesrated_on
1EarbudsAsha4122026-03-01
2EarbudsBilal5302026-03-02
3EarbudsChenNULL02026-03-03
4EarbudsDina322026-03-04
5SpeakerEshan282026-03-02
6SpeakerFatima4152026-03-05
7SpeakerGaurav5402026-03-06
8ChargerHema512026-03-03
9ChargerIshan532026-03-07
query.sql 
SELECT AVG(stars) AS avg_stars, ROUND(AVG(stars), 2) AS rounded FROM ratings;

Result: 1 row

avg_starsrounded
4.12504.13
AVG of integers returns a DECIMAL with 4 extra decimal places. Use ROUND(AVG(x), n) for display.

2. AVG and NULL

How NULL changes an averageRatings 4, 5, NULL and 3. AVG(rating) ignores the NULL and returns 4.0 (12 divided by 3). Treating NULL as zero with COALESCE gives 3.0 (12 divided by 4).ratingrating45NULL3AVG(rating)(4 + 5 + 3) / 3 = 4.0000AVG(COALESCE(rating, 0))(4 + 5 + 0 + 3) / 4 = 3.0000NULL left outof the countNULL countedas zero
Figure 1: AVG divides by the number of non-NULL values. Decide whether missing means "unknown" or "zero".
query.sql 
SELECT AVG(stars)              AS ignores_null,
       AVG(COALESCE(stars, 0)) AS null_as_zero,
       SUM(stars) / COUNT(*)   AS sum_over_all_rows,
       SUM(stars) / COUNT(stars) AS sum_over_rated
FROM ratings WHERE product = 'Earbuds';

Result: 1 row

ignores_nullnull_as_zerosum_over_all_rowssum_over_rated
4.00003.00003.00004.0000

3. Average per Group

query.sql 
SELECT product, COUNT(stars) AS ratings, ROUND(AVG(stars), 2) AS avg_stars FROM ratings GROUP BY product ORDER BY avg_stars DESC;

Result: 3 rows

productratingsavg_stars
Charger25.00
Earbuds34.00
Speaker33.67
query.sql 
SELECT product, ROUND(AVG(stars), 2) AS avg_stars FROM ratings GROUP BY product HAVING AVG(stars) >= 4;

Result: 2 rows

productavg_stars
Earbuds4.00
Charger5.00

4. AVG(DISTINCT)

query.sql 
SELECT AVG(stars) AS avg_all, AVG(DISTINCT stars) AS avg_of_distinct_values FROM ratings;

Result: 1 row

avg_allavg_of_distinct_values
4.12503.5000

AVG(DISTINCT) averages each different value once (2, 3, 4, 5 here). It answers a different question and is rarely what a report needs.

5. Weighted Averages

Give helpful reviews more weight: multiply each rating by its votes, then divide by the total votes:

weighted.sql 
SELECT product,
       ROUND(AVG(stars), 2)                                   AS simple_avg,
       ROUND(SUM(stars * helpful_votes) / SUM(helpful_votes), 2) AS weighted_avg
FROM ratings
WHERE stars IS NOT NULL
GROUP BY product;

Result: 3 rows

productsimple_avgweighted_avg
Earbuds4.004.64
Speaker3.674.38
Charger5.005.00

6. Comparing Rows with the Average

query.sql 
SELECT reviewer, product, stars
FROM ratings
WHERE stars > (SELECT AVG(stars) FROM ratings)
ORDER BY stars DESC, reviewer;

Result: 4 rows

reviewerproductstars
BilalEarbuds5
GauravSpeaker5
HemaCharger5
IshanCharger5

Per product, using a window function so every row keeps its details:

vs-average.sql 
SELECT product, reviewer, stars,
       ROUND(AVG(stars) OVER (PARTITION BY product), 2)         AS product_avg,
       stars - ROUND(AVG(stars) OVER (PARTITION BY product), 2) AS vs_avg
FROM ratings
WHERE stars IS NOT NULL
ORDER BY product, stars DESC;

Result: 8 rows

productreviewerstarsproduct_avgvs_avg
ChargerHema55.000.00
ChargerIshan55.000.00
EarbudsBilal54.001.00
EarbudsAsha44.000.00
EarbudsDina34.00-1.00
SpeakerGaurav53.671.33
SpeakerFatima43.670.33
SpeakerEshan23.67-1.67

7. Moving Averages

A moving average smooths out daily ups and downs. Average of the current and two previous ratings, by date:

moving-average.sql 
SELECT rated_on, product, stars,
       ROUND(AVG(stars) OVER (ORDER BY rated_on, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM ratings
WHERE stars IS NOT NULL
ORDER BY rated_on, id;

Result: 8 rows

rated_onproductstarsmoving_avg_3
2026-03-01Earbuds44.00
2026-03-02Earbuds54.50
2026-03-02Speaker23.67
2026-03-03Charger54.00
2026-03-04Earbuds33.33
2026-03-05Speaker44.00
2026-03-06Speaker54.00
2026-03-07Charger54.67

8. Pitfalls

  • An average of averages isn't the overall average when groups differ in size; average the raw rows or use weights.
  • Averages hide outliers; check MIN, MAX or a median as well.
  • AVG of an empty set is NULL; use COALESCE if you need 0.
avg-of-avg.sql 
SELECT ROUND(AVG(avg_stars), 4) AS average_of_averages,
       (SELECT ROUND(AVG(stars), 4) FROM ratings) AS true_average
FROM (SELECT product, AVG(stars) AS avg_stars FROM ratings GROUP BY product) t;

Result: 1 row

average_of_averagestrue_average
4.22224.1250

9. Summary

  • AVG(col) = sum of non-NULL values ÷ number of non-NULL values.
  • Use COALESCE if missing values should count as 0; ROUND for display.
  • Group with GROUP BY, filter averages with HAVING.
  • Weighted average = SUM(value × weight) ÷ SUM(weight); window AVG gives per-row comparisons and moving averages.