Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore null values. Aggregate functions are often used together with the GROUP BY clause of the SELECT statement.
All aggregate functions are deterministic. Whenever an aggregation function is called with a set of given input values, the same value is returned.
Each aggregate function of OceanBase Database can have only one value expression. For example, COUNT(c1, c2) is not supported. Only COUNT(c1) is supported.
AVG
Declaration
AVG(([DISTINCT] expr)
Description
This function returns the average value in a specified group, with null values ignored. The DISTINCT option can be used to return the average value of different values of expr. If no matching row is found, AVG() returns NULL.
Example
obclient> select * from oceanbasetest;
+----+------+------+
| id | ip | ip2 |
+----+------+------+
| 1 | 4 | NULL |
| 3 | 3 | NULL |
| 4 | 3 | NULL |
+----+------+------+
3 rows in set (0.01 sec)
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 (0.00 sec)
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 (0.00 sec)
COUNT
Declaration
COUNT([DISTINCT] expr)
Description
This function returns the number of non-NULL values in rows retrieved by the SELECT statement. If no matching row is found, COUNT() returns 0. The DISTINCT option can be used to return the number of different values of expr.
COUNT(*) differs from other options in that it returns the number of retrieved rows, regardless of whether the rows contain NULL values.
Example
obclient> select * from oceanbasetest;
+----+------+------+
| id | ip | ip2 |
+----+------+------+
| 1 | 4 | NULL |
| 3 | 3 | NULL |
| 4 | 3 | NULL |
+----+------+------+
3 rows in set (0.00 sec)
obclient> select count(ip2), count(ip), count(distinct(ip)), count(*) from oceanbasetest;
+------------+-----------+---------------------+----------+
| count(ip2) | count(ip) | count(distinct(ip)) | count(*) |
+------------+-----------+---------------------+----------+
| 0 | 3 | 2 | 3 |
+------------+-----------+---------------------+----------+
1 row in set (0.00 sec)
MAX
Declaration
MAX([DISTINCT] expr)
Description
This function returns the maximum value in the specified data set.
The value of MAX() can be a string parameter, in which case the maximum string value is returned. The DISTINCT keyword can be used to retrieve the maximum of the distinct values of expr. However, the result is the same as omittingDISTINCT.
Assume that Table a has three data rows: id=1,num=10; id=2,num=20; and id=3,num=30.
Example
obclient> SELECT MAX(num) FROM a;
+-----------------+
| MAX(num) |
+-----------------+
| 30 |
+-----------------+
1 row in set (0.00 sec)
MIN
Declaration
MIN([DISTINCT] expr)
Description
This function returns the minimum value in the specified data set.
The argument of MAX() can be a string, in which case the minimum string value is returned. The DISTINCT keyword can be used to retrieve the minimum of distinct values of expr. However, the result is the same as omitting DISTINCT.
Assume that Table a has three data rows: id=1,num=10; id=2,num=20; and id=3,num=30.
Example
obclient> SELECT MIN(num) FROM a;
+----------------+
| MIN(num) |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)
SUM
Declaration
SUM([DISTINCT] expr)
Description
This function returns the sum of expr. If the returned set does not contain any rows, SUM() returns NULL. The DISTINCT keyword can be used to find the sum of distinct values of expr.
If no matching row is found, SUM() returns NULL.
Example
obclient> select * from oceanbasetest;
+------+------+------+
| id | ip | ip2 |
+------+------+------+
| 1 | 4 | NULL |
| 3 | 3 | NULL |
| 4 | 3 | NULL |
+------+------+------+
3 rows in set (0.00 sec)
obclient> select sum(ip2),sum(ip),sum(distinct(ip)) from oceanbasetest;
+----------+---------+-------------------+
| sum(ip2) | sum(ip) | sum(distinct(ip)) |
+----------+---------+-------------------+
| NULL | 10 | 7 |
+----------+---------+-------------------+
1 row in set (0.00 sec)
GROUP_CONCAT
Declaration
GROUP_CONCAT([DISTINCT] expr)
Description
This function returns a string of concatenated non-NULL values from a group.
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
ASC | DESC]
[SEPARATOR str_val])
Example
obclient> select * from book; // Table book (book number, book name, and publisher)
+--------+--------------------------------+-----------------------------+
| bookid | bookname | publishername |
+--------+--------------------------------+-----------------------------+
| 1 | GiT Help | Alibaba Group |
| 2 | MySQL Performance Optimization | Zhejiang University Press |
| 3 | JAVA Programming Guide | China Machine Press |
| 3 | JAVA Programming Guide | China Machine Press |
| 4 | Large-Scale Distributed Storage System | China Machine Press |
+--------+--------------------------------+-----------------------------+
5 rows in set (0.00 sec)
//Search for book name
obclient> select group_concat(bookname) from book group by bookname;
+-----------------------------------+
| group_concat(bookname) |
+-----------------------------------+
| GiT Help |
| JAVA Programming Guide, JAVA Programming Guide |
| MySQL Performance Optimization |
| Large-Scale Distributed Storage System |
+-----------------------------------+
4 rows in set (0.00 sec)
//Search for the book name. The book name is unique.
obclient> select group_concat(distinct(bookname)) from book group by bookname;
+----------------------------------+
| group_concat(distinct(bookname)) |
+----------------------------------+
| GIT Help |
| JAVA Programming Guide |
| MySQL Performance Optimization |
| Large-Scale Distributed Storage System |
+----------------------------------+
4 rows in set (0.01 sec)
//Search for the book name and publisher information, group the information by book name, and display the publisher information in descending order.
obclient> select bookname, group_concat(publishername order by publishername desc separator ';' ) from book group by bookname;
+--------------------------------+---------------------------------------------------------------------+
| bookname | group_concat(publishername order by publishername desc separator ';' ) |
+--------------------------------+---------------------------------------------------------------------+
| GiT Help | Alibaba Group |
| JAVA Programming Guide | China Machine Press; China Machine Press ||
| MySQL Performance Optimization | Zhejiang University Press |
| Large-Scale Distributed Storage System | China Machine Press |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)