MariaDB AVG() Function

In MariaDB, AVG() is a built-in function that returns the average of a given expression.

MariaDB AVG() Syntax

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

AVG(expr)
AVG(DISTINCT expr)

We typically use the MariaDB AVG() function in SQL statements as follows:

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

The expr expression is used for aggregation operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

The expression or column name used for grouping.

Return value

The MariaDB AVG(expr) function returns the average of an expression. The AVG(DISTINCT expr) function returns the average of the unique values ​​in expr.

The AVG() function ignores the NULL values.

If there is no matching row, the AVG() function returns NULL.

MariaDB AVG() Examples

Example data

We’ll demonstrate with the student_score table. Let’s first create a demo table and insert test rows.

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 is all the rows in the table:

SELECT * from student_score;

Output:

+----+------+---------+-------+
| 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 |
+----+------+---------+-------+

Basic Example

The following SQL statement returns the average grade of all grades.

SELECT AVG(score) from student_score;

Output:

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

If you need to calculate the average of all unique grades, use the following statement with DISTINCT keyword:

SELECT AVG(DISTINCT score) from student_score;

Output:

+---------------------+
| AVG(DISTINCT score) |
+---------------------+
|             89.6000 |
+---------------------+

GROUP BY

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

1. Calculate average score for each subject

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

   Output:

   +---------+------------+
   | 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 the average score for each person

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

   Output:

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

   Here, MySQL will GROUP BY namefirst name group all rows by and then execute within each group AVG(score).

Subquery

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

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

Output:

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

Note that we use the following subquery statement to query the average grade:

SELECT AVG(score)
FROM student_score

Conclusion

In MariaDB, AVG() is a built-in function that returns the average of a given expression.