All Products
Search
Document Center

Aggregate functions

Last Updated: Jun 18, 2021

Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore NULL values. In most cases, aggregate functions are used in conjunction with GROUP BY clauses in SELECT statements.

All aggregate functions are deterministic. Each time you call aggregate functions by using the same set of input values, the aggregate functions return the same value.

In ApsaraDB for OceanBase, you can specify only one expression value for each aggregate function. For example, COUNT(c1, c2) is not supported, and only COUNT(c1) is supported.

AVG

Declaration

AVG(([DISTINCT] expr)

Description

This function returns the average value of a specified data set. The function ignores the NULL values in the specified data set. The DISTINCT keyword is used to return the average value of the distinct expr values. If no matched rows are found, the AVG() function returns NULL.

Example

Oceanbase>select * from oceanbasetest;
+----+------+------+
| id | ip   | ip2  |
+----+------+------+
|  1 |    4 | NULL |
|  3 |    3 | NULL |
|  4 |    3 | NULL |
+----+------+------+
3 rows in set (0.01 sec)

Oceanbase>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)

Oceanbase>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 the rows retrieved by the SELECT statement. If no matched rows are found, COUNT() returns 0. The DISTINCT keyword is used to return the number of the distinct expr values.

COUNT(*) returns the number of retrieved rows, regardless of whether they contain NULL values.

Example

Oceanbase>select * from oceanbasetest;
+----+------+------+
| id | ip   | ip2  |
+----+------+------+
|  1 |    4 | NULL |
|  3 |    3 | NULL |
|  4 |    3 | NULL |
+----+------+------+
3 rows in set (0.00 sec)

Oceanbase>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 of a specified data set.

You can specify strings in the MAX() function. In this case, the maximum string is returned. The DISTINCT keyword can be used to return the maximum value of the distinct expr values. MAX() returns the same maximum value regardless of whether you omit the DISTINCT keyword.

Assume that table a has three rows. For the id column, the values are 1, 2, and 3. For the num column, the values are 10, 20, and 30.

Example

Oceanbase>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 of a specified data set.

You can specify strings in the MIN() function. In this case, the minimum string is returned. The DISTINCT keyword can be used to return the minimum value of the distinct expr values. MIN() returns the same minimum value regardless of whether you omit the DISTINCT keyword.

Assume that table a has three rows. For the id column, the values are 1, 2, and 3. For the num column, the values are 10, 20, and 30.

Example

Oceanbase>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 value of the expr values. If no rows are found in expr, the SUM() function returns NULL. You can use the DISTINCT keyword to return the sum value of the distinct expr values.

If no matched rows are found, the SUM() function returns NULL.

Example

Oceanbase>select * from oceanbasetest;
+------+------+------+
| id   | ip   | ip2  |
+------+------+------+
|    1 |    4 | NULL |
|    3 |    3 | NULL |
|    4 |    3 | NULL |
+------+------+------+
3 rows in set (0.00 sec)

Oceanbase>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 concatenates non-NULL strings from a specified group into a single string.

GROUP_CONCAT([DISTINCT] expr [,expr ...]
    [ORDER BY {unsigned_integer | col_name | expr}
     ASC | DESC]
    [SEPARATOR str_val])

Example

Oceanbase> select * from book; //The table named book (book number, book title, publisher)
+--------+--------------------------------+-----------------------------+
| bookid | bookname                       | publishname                 |
+--------+--------------------------------+-----------------------------+
|      1 | git help                       | alibaba group publisher     |
|      2 | MySQL Optimization                  | Zhejiang University Press          |
|      3 | Java Programming Guide                   | Machinery Industry Press              |
|      3 | Java Programming Guide                       | Machinery Industry Press   |
|     4 | Large-Scale Distributed Storage System           | Machinery Industry Press              |
+--------+--------------------------------+-----------------------------+
5 rows in set (0.00 sec)    

//Retrieve book titles.
Oceanbase>select group_concat(bookname) from book group by bookname;
+-----------------------------------+
| group_concat(bookname)            |
+-----------------------------------+
| git help                          |
| Java Programming Guide, Java Programming Guide         |
| MySQL Optimization                     |
| Large-Scale Distributed Storage System              |
+-----------------------------------+
4 rows in set (0.00 sec)

//Retrieve distinct book titles.
Oceanbase>select group_concat(distinct(bookname)) from book group by bookname;
+----------------------------------+
| group_concat(distinct(bookname)) |
+----------------------------------+
| git help                         |
| Java Programming Guide                     |
| MySQL Optimization                    |
| Large-Scale Distributed Storage System             |
+----------------------------------+
4 rows in set (0.01 sec)    

//Query book titles and publisher information, group the books by book title, and sort the publisher names in reverse alphabetical order.
Oceanbase>select bookname, group_concat(publishname order by publishname desc separator  ';' ) from book group by bookname;
+--------------------------------+---------------------------------------------------------------------+
| bookname                       | group_concat(publishname order by publishname desc separator  ';' ) |
+--------------------------------+---------------------------------------------------------------------+
| git help                       | alibaba group publisher                                             |
| Java Programming Guide                   | Mechanical Industry Press, Mechanical Industry Press                                       |
| MySQL Optimization                  | Zhejiang University Press                                                  |
| Large-Scale Distributed Storage System           | Machinery Industry Press                                                      |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)