Declaration
AVG(([DISTINCT | ALL] expr)
Description
Returns the average value in 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
obclient> SELECT * FROM oceanbasetest;
+----+------+------+
| id | ip | ip2 |
+----+------+------+
| 1 | 4 | NULL |
| 3 | 3 | NULL |
| 4 | 3 | NULL |
+----+------+------+
3 rows in set
obclient> 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
obclient> 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