All Products
Search
Document Center

Aggregate functions

Last Updated: Jan 04, 2020

Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore all null values. Aggregation functions are often used with the GROUP BY clause in a SELECT statement.

All aggregate functions are deterministic. When they are called with the same set of input values, they return the same value.

Aggregate functions in ApsaraDB for OceanBase allows only one input parameter. For example, if COUNT(c1, c2) is not supported, only COUNT(c1) is supported.

AVG

Syntax

AVG([DISTINCT] expr)

Description

Returns the average in the specified data set, null values are ignored. The DISTINCT keyword can be used to return the average of the distinct values in expr. If no matching row is found in expr, AVG() returns NULL.

Example:

  1. Oceanbase>select * from oceanbasetest;
  2. +----+------+------+
  3. | id | ip | ip2 |
  4. +----+------+------+
  5. | 1 | 4 | NULL |
  6. | 3 | 3 | NULL |
  7. | 4 | 3 | NULL |
  8. +----+------+------+
  9. 3 rows in set (0.01 sec)
  10. Oceanbase>select avg(ip2), avg(ip), avg(distinct(ip)) from oceanbasetest;
  11. +----------+---------+-------------------+
  12. | avg(ip2) | avg(ip) | avg(distinct(ip)) |
  13. +----------+---------+-------------------+
  14. | NULL | 3.3333 | 3.5000 |
  15. +----------+---------+-------------------+
  16. 1 row in set (0.00 sec)
  17. Oceanbase>select avg(distinct(ip)),avg(ip),avg(ip2) from oceanbasetest;
  18. +-------------------+---------+----------+
  19. | avg(distinct(ip)) | avg(ip) | avg(ip2) |
  20. +-------------------+---------+----------+
  21. | 3.5000 | 3.3333 | NULL |
  22. +-------------------+---------+----------+
  23. 1 row in set (0.00 sec)

COUNT

Syntax

COUNT([DISTINCT] expr)

Description

COUNT([DISTINCT] expr) returns the number of non-NULL values in the rows retrieved by the SELECT statement. If no matching row is found, COUNT() returns 0. The DISTINCT keyword can be used to return the number of distinct values in expr.

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

Example:

  1. Oceanbase>select * from oceanbasetest;
  2. +----+------+------+
  3. | id | ip | ip2 |
  4. +----+------+------+
  5. | 1 | 4 | NULL |
  6. | 3 | 3 | NULL |
  7. | 4 | 3 | NULL |
  8. +----+------+------+
  9. 3 rows in set (0.00 sec)
  10. Oceanbase>select count(ip2), count(ip), count(distinct(ip)), count(*) from oceanbasetest;
  11. +------------+-----------+---------------------+----------+
  12. | count(ip2) | count(ip) | count(distinct(ip)) | count(*) |
  13. +------------+-----------+---------------------+----------+
  14. | 0 | 3 | 2 | 3 |
  15. +------------+-----------+---------------------+----------+
  16. 1 row in set (0.00 sec)

MAX

Syntax

MAX([DISTINCT] expr)

Description

Returns the maximum of the specified data.

MAX() can take a string parameter. The strings are sorted based on alphabetical order and the maximum string is returned. The DISTINCT keyword can be used to find the maximum value of different values in expr. However, this produces the same result as omitting DISTINCT.

For example, table a has three rows of data: id = 1, num = 10; id = 2, num = 20; id = 3, num = 30.

Example:

  1. Oceanbase>SELECT MAX(num) FROM a;
  2. +-----------------+
  3. | MAX(num) |
  4. +-----------------+
  5. | 30 |
  6. +-----------------+
  7. 1 row in set (0.00 sec)

MIN

Syntax

MIN([DISTINCT] expr)

Description

Returns the minimum of the specified data.

MIN() can take a string parameter. The strings are sorted based on alphabetical order and the minimum string is returned. The DISTINCT keyword can be used to find the minimum of different values in expr. However, this produces the same result as omitting DISTINCT.

For example, table a has three rows of data: id = 1, num = 10; id = 2, num = 20; id = 3, num = 30.

Example:

  1. Oceanbase>SELECT MIN(num) FROM a;
  2. +----------------+
  3. | MIN(num) |
  4. +----------------+
  5. | 10 |
  6. +----------------+
  7. 1 row in set (0.00 sec)

SUM

Syntax

SUM([DISTINCT] expr)

Description

Returns the total sum of expr. If expr has no rows, the SUM() function returns NULL. The DISTINCT keyword can be used to find the sum of different values in expr.

If expr has no rows, the SUM() function returns NULL.

Example:

  1. Oceanbase>select * from oceanbasetest;
  2. +------+------+------+
  3. | id | ip | ip2 |
  4. +------+------+------+
  5. | 1 | 4 | NULL |
  6. | 3 | 3 | NULL |
  7. | 4 | 3 | NULL |
  8. +------+------+------+
  9. 3 rows in set (0.00 sec)
  10. Oceanbase>select sum(ip2),sum(ip),sum(distinct(ip)) from oceanbasetest;
  11. +----------+---------+-------------------+
  12. | sum(ip2) | sum(ip) | sum(distinct(ip)) |
  13. +----------+---------+-------------------+
  14. | NULL | 10 | 7 |
  15. +----------+---------+-------------------+
  16. 1 row in set (0.00 sec)

GROUP_CONCAT

Syntax

GROUP_CONCAT([DISTINCT] expr)

Description

This function concatenates non-NULL strings from a group into a single string.

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

Example:

  1. Oceanbase> select * from book; // Table book (book number, book name, publishername)
  2. +--------+--------------------------------+-----------------------------+
  3. | bookid | bookname | publishname |
  4. +--------+--------------------------------+-----------------------------+
  5. | 1 | git help | alibaba group publisher |
  6. | 2 | MySQL Optimization | Zhejiang University Press |
  7. | 3 | JAVA Programming Guide | Machinery Industry Press |
  8. | 3 | JAVA Programming Guide | Machinery Industry Press |
  9. | 4 | Large-Scale Distributed Storage System | Machinery Industry Press |
  10. +--------+--------------------------------+-----------------------------+
  11. 5 rows in set (0.00 sec)
  12. // Find book title information
  13. Oceanbase>select group_concat(bookname) from book group by bookname;
  14. +-----------------------------------+
  15. | group_concat(bookname) |
  16. +-----------------------------------+
  17. | git help |
  18. | JAVA Programming Guide,JAVA Programming Guide |
  19. | MySQL Optimization |
  20. | Large-Scale Distributed Storage System |
  21. +-----------------------------------+
  22. 4 rows in set (0.00 sec)
  23. //Find the title information, the title is unique
  24. Oceanbase>select group_concat(distinct(bookname)) from book group by bookname;
  25. +----------------------------------+
  26. | group_concat(distinct(bookname)) |
  27. +----------------------------------+
  28. | git help |
  29. | JAVA Programming Guide |
  30. | MySQL Optimization................... |
  31. | Large-Scale Distributed Storage System |
  32. +----------------------------------+
  33. 4 rows in set (0.01 sec)
  34. //Query book titles and publisher information, group the books by book title, and sort the publisher names in the reverse alphabetical order
  35. Oceanbase>select bookname, group_concat(publishname order by publishname desc separator ';' ) from book group by bookname;
  36. +--------------------------------+---------------------------------------------------------------------+
  37. | bookname | group_concat(publishname order by publishname desc separator ';' ) |
  38. +--------------------------------+---------------------------------------------------------------------+
  39. | git help | alibaba group publisher |
  40. | JAVA Programming Guide | Mechanical Industry Press; Mechanical Industry Press |
  41. | MySQL Performance Optimization | Zhejiang University Press |
  42. | Large-Scale Distributed Storage System | Machinery Industry Press |
  43. +--------------------------------+---------------------------------------------------------------------+
  44. 4 rows in set (0.00 sec)