MySQL AVG(): Calculate the Average Value of a Numeric Column

MySQL AVG() returns the arithmetic mean of the non-NULL numeric values in an expression. It is commonly used for averages such as marks, prices, ratings, quantities, and grouped report values.

SELECT AVG(mark) AS avg_mark
FROM student;

For the six sample rows used below, the result is:

AVG() Syntax Top ↑

SELECT AVG(numeric_expression)
FROM table_name;

AVG() ignores NULL values and divides the sum of the remaining numeric values by the number of non-NULL values.

Sample Table and Basic Average Top ↑

The first examples use these rows from the student table:

SELECT AVG(mark) AS avg_mark
FROM student;

AVG() with WHERE Top ↑

A WHERE clause filters rows before AVG() is calculated.

SELECT AVG(mark) AS avg_mark
FROM student
WHERE mark > 55;

Only marks greater than 55 contribute to the result.

AVG() with GROUP BY Top ↑

Use GROUP BY when you need one average for each class rather than one average for the complete table.

SELECT class,
       AVG(mark) AS avg_mark
FROM student
GROUP BY class
ORDER BY class;

WHERE before GROUP BY Top ↑

You can first remove unwanted rows and then calculate an average for each remaining group:

SELECT class,
       AVG(mark) AS avg_mark
FROM student
WHERE mark > 55
GROUP BY class
ORDER BY class;

Filter Group Averages with HAVING Top ↑

WHERE filters individual rows before grouping. HAVING filters the grouped result after AVG() has been calculated.

SELECT class,
       AVG(mark) AS avg_mark
FROM student
GROUP BY class
HAVING AVG(mark) > 75
ORDER BY avg_mark DESC;

This returns only classes whose calculated average mark is greater than 75.

AVG() with IN Top ↑

To calculate the average for selected rows, combine IN with AVG():

SELECT AVG(mark) AS avg_mark
FROM student
WHERE id IN (1, 2, 3, 4);

For IDs 1 through 4 in the sample table, the average is 68.7500.

AVG() in a Subquery Top ↑

A subquery can calculate an average that is then used by the outer query. For example, list students in class Six whose mark is below the class average:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE class = 'Six'
  AND mark < (
      SELECT AVG(mark)
      FROM student
      WHERE class = 'Six'
  )
ORDER BY mark;

The inner query first calculates the class average. The outer query then returns only students below that value.

Read more about SQL subqueries

How AVG() Handles NULL Top ↑

AVG() ignores NULL values. NULL is not treated as zero.

The average is:

(50 + 50) / 2 = 50

It is not 100 / 4 = 25, because the two NULL values are not part of the denominator.

See SQL NULL values for the difference between NULL, zero, and an empty value.

AVG(DISTINCT) Top ↑

AVG(DISTINCT expression) averages each distinct non-NULL value only once.

CREATE TABLE plus2_price (
    product VARCHAR(20) NOT NULL,
    price_date DATE NOT NULL,
    price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO plus2_price
    (product, price_date, price)
VALUES
    ('Product1', '2017-02-24', 10),
    ('Product1', '2017-02-24', 10),
    ('Product1', '2017-02-24', 10),
    ('Product1', '2017-02-25', 20);

The average of all four rows is:

SELECT AVG(price) AS avg_price
FROM plus2_price;

Result: 12.50.

If the requirement is specifically to average the unique price values 10 and 20:

SELECT AVG(DISTINCT price) AS avg_unique_price
FROM plus2_price;

Result: 15.00.

Average by day Top ↑

If the requirement is to give each day equal weight, first calculate one average per day and then average those daily values:

SELECT AVG(daily_avg) AS avg_of_daily_prices
FROM (
    SELECT price_date,
           AVG(price) AS daily_avg
    FROM plus2_price
    GROUP BY price_date
) AS daily_prices;

This distinction is important whenever repeated measurements exist within each day or category.

Read more about DISTINCT

Average Across Columns in One Row Top ↑

Suppose student3 stores marks for three subjects in separate columns. To calculate each student's total and average:

SELECT id,
       name,
       social,
       math,
       science,
       social + math + science AS total,
       (social + math + science) / 3.0 AS avg_mark
FROM student3
ORDER BY id;

Store Calculated Averages Carefully Top ↑

Calculated values can usually be derived when needed. Storing the same average in another column creates duplicated data that can become stale when a mark changes.

If a reporting table intentionally stores the student's three-subject average, the direct calculation is simpler than joining the table to itself:

UPDATE student3_avg
SET average = (social + math + science) / 3.0;

For class averages, calculate the aggregate from the source rows with AVG() and GROUP BY. Store it only when there is a clear reporting, caching, or historical requirement.

Download the existing sample table dump used for the average examples.

The existing calculated-value insertion tutorial shows another reporting-table approach.

Order Rows by Calculated Average Top ↑

Use ORDER BY on the calculated alias to display the highest average first:

SELECT id,
       name,
       social,
       math,
       science,
       social + math + science AS total,
       (social + math + science) / 3.0 AS avg_mark
FROM student3
ORDER BY avg_mark DESC, id;

No GROUP BY id is needed here because each source row already represents one student.

PHP PDO Example Top ↑

After connecting to MySQL with PDO, a static SELECT query can be executed with query():

<?php
$sql="SELECT id,
             name,
             social,
             math,
             science,
             social + math + science AS total,
             (social + math + science) / 3.0 AS avg_mark
      FROM student3
      ORDER BY avg_mark DESC, id";

$stmt=$dbo->query($sql);

foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['name'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' : '
        .htmlspecialchars(
            (string)$row['avg_mark'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .'</p>';
}

The SQL is static, so no prepared parameter is required in this example. When user input is added to the query, use a prepared statement and bind the values.

Formatting the Average Result Top ↑

MySQL FORMAT() can format a number for display:

SELECT id,
       name,
       FORMAT(
           (social + math + science) / 3.0,
           2
       ) AS avg_display
FROM student3;

See SQL FORMAT() for more examples.

Common AVG() Mistakes Top ↑

Treating NULL as zero Top ↑

AVG() ignores NULL values. Replacing NULL with zero changes both the sum and the denominator, so only do that when zero is the intended business meaning.

Using GROUP BY when each row is already the required unit Top ↑

If one row already contains one student's three subject marks, calculate the row expression directly. GROUP BY id is unnecessary unless multiple source rows must actually be aggregated.

Assuming AVG(DISTINCT price) means average by date Top ↑

DISTINCT removes duplicate numeric values, not duplicate days. Group by date when the requirement is date-based.

Using WHERE to filter an aggregate result Top ↑

Use WHERE before aggregation and HAVING after GROUP BY when filtering by the calculated average.

Formatting too early Top ↑

Keep averages numeric while sorting, comparing, or calculating. Format them for display at the end.

Storing derived averages without a reason Top ↑

Stored averages can become inconsistent with the source marks. Calculate them on demand unless a reporting or historical requirement justifies storage.

Related Tutorials and Downloads Top ↑

Download the sample average-table SQL dump
Download the student3 SQL dump

Other filters such as BETWEEN can also be combined with AVG() when the average must be calculated for a selected range.

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: