AVG

Declaration

AVG(([DISTINCT | ALL] expr)

Description

Returns the average of the specified group, ignoring null values. The DISTINCT option can be used to return the average of distinct values of expr. If no matching rows are found, AVG() returns NULL.

Examples

SELECT * FROM oceanbasetest;
+----+------+------+
| id | ip   | ip2  |
+----+------+------+
|  1 |    4 | NULL |
|  3 |    3 | NULL |
|  4 |    3 | NULL |
+----+------+------+
3 rows in set (0.002 sec)

SELECT avg(ip2), avg(ip), avg(distinct(ip)) FROM oceanbasetest;
+----------+---------+-------------------+
| avg(ip2) | avg(ip) | avg(distinct(ip)) |
+----------+---------+-------------------+
|     NULL |  3.3333 |            3.5000 |
+----------+---------+-------------------+
1 row in set (0.001 sec)

SELECT avg(distinct(ip)),avg(ip),avg(ip2) FROM oceanbasetest;
+-------------------+---------+----------+
| avg(distinct(ip)) | avg(ip) | avg(ip2) |
+-------------------+---------+----------+
|            3.5000 |  3.3333 |     NULL |
+-------------------+---------+----------+
1 row in set (0.001 sec)