MySQL AVG() Function

MySQL AVG() function returns the average of all the values ​​represented by the expression.

AVG() Syntax

Here is the syntax of the MySQL AVG() function:

AVG(expr)
AVG(DISTINCT expr)

We usually use the AVG() function like this:

SELECT AVG(expr), ...
FROM table_name
[WHERE ...];

Or use the AVG() function with the GROUP BY clause:

SELECT AVG(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr

An expression used for aggregate operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

An expression or column name to use for grouping.

Return value

The AVG(expr) function returns the average of all the values ​​represented by the expression. The AVG(DISTINCT expr) function returns the average of the unique values ​​represented by expr.

The AVG() function ignores nulls.

If there are no matching rows, the AVG() function returns NULL.

AVG() Examples

We’ll demonstrate this function with a table named student_score. Let’s create the table and insert some rows firstly.

CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);
INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Jim', 'Math', 84),
    ('Jim', 'English', 96),
    ('Tim', 'Math', 80),
    ('Tim', 'English', 98);

Here are the rows in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Jim  | Math    |    84 |
|  4 | Jim  | English |    96 |
|  5 | Tim  | Math    |    80 |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

AVG() Basic example

The following SQL statement returns the average of all scores.

SELECT AVG(score) from student_score;

+------------+
| AVG(score) |
+------------+
|    88.0000 |
+------------+

AVG() and GROUP BY Example

Let’s use AVG() and GROUP BY to calculate the average scores from more dimensions.

  1. Calculate average score for each subject:

    SELECT subject, AVG(score)
FROM student_score
GROUP BY subject;

    +---------+------------+
| subject | AVG(score) |
+---------+------------+
| Math    |    81.3333 |
| English |    94.6667 |
+---------+------------+

    Here, MySQL will first group the rows by subject according to GROUP BY subject, and then execute AVG(score) within each group.

  2. Calculate average score for each student

    SELECT name, AVG(score)
FROM student_score
GROUP BY name;

    +------+------------+
| name | AVG(score) |
+------+------------+
| Tom  |    85.0000 |
| Jim  |    90.0000 |
| Tim  |    89.0000 |
+------+------------+

    Here, MySQL will first group the rows by name according to GROUP BY name, and then execute AVG(score) within each group.

Using AVG() in Subqueries

If you want to query for rows with score above the average, you can use the following SQL statement:

SELECT *
FROM student_score
WHERE score > (
        SELECT AVG(score)
        FROM student_score
    );

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  2 | Tom  | English |    90 |
|  4 | Jim  | English |    96 |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

Note that here we get the average score through the following sub-query:

SELECT AVG(score)
FROM student_score