All Products
Search

This Product

    :Aggregate functions

    Document Center

    :Aggregate functions

    Last Updated:Jul 01, 2024

    This topic describes the aggregate functions supported by AnalyticDB for MySQL.

    AnalyticDB for MySQL supports the following aggregate functions:

    • ARBITRARY: randomly returns a value from a set of data.

    • AVG: calculates the average value for a set of numeric values.

    • BIT_AND: returns the result of a bitwise AND operation on all bits of the input values.

    • BIT_OR: returns the result of a bitwise OR operation on all bits of the input values.

    • BIT_XOR: returns the result of a bitwise XOR operation on all bits of the input values.

    • COUNT: counts the number of records returned by a SELECT query.

    • MAX: calculates the maximum value of a set of numeric values.

    • MIN: calculates the minimum value of a set of numeric values.

    • STD, STDDEV, or STDDEV_POP: returns the population standard deviation of all input values.

    • STDDEV_SAMP: returns the sample standard deviation for a set of integers, decimals, or floating-point numbers.

    • SUM: calculates the sum of all input values.

    • VARIANCE (non-standard SQL function): returns the population standard variance for a set of integers, decimals, or floating-point numbers.

    • VAR_POP (standard SQL function): returns the population standard variance for a set of integers, decimals, or floating-point numbers.

    • VAR_SAMP: returns the sample variance for a set of integers, decimals, or floating-point numbers.

    • GROUP_CONCAT: returns a string result of the concatenated values from a group. The values come from the returned results of the GROUP BY clause.

    Note

    In this topic, a table named testtable is used for all aggregate functions except the GROUP_CONCAT function. The following statement is used to create the table:

    CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);

    The following statement is used to insert test data into the testtable table:

    INSERT INTO testtable VALUES (1),(2),(3);

    ARBITRARY

    arbitrary(x)

    • Description: This function randomly returns a value from a set of data.

    • Data type of the input value: all data types.

    • Data type of the return value: consistent with the data type of the input value of this function.

    • Example 

      SELECT arbitrary(a) FROM testtable;

      The following result is returned:

      +--------------+
| arbitrary(a) |
+--------------+
|            2 |
+--------------+

    AVG

    avg(x)

    • Description: This function calculates the average value for a set of numeric values.

    • Data type of the input value: BIGINT, DOUBLE, or FLOAT.

    • Data type of the return value: DOUBLE.

    • Example 

      SELECT avg(a) FROM testtable;

      The following result is returned:

      +--------+
| avg(a) |
+--------+
|    2.0 |
+--------+

    BIT_AND

    bit_and(x)

    • Description: This function returns the result of a bitwise AND operation on all bits of the input values.

    • Data type of the input value: BIGINT, DOUBLE, or FLOAT.

    • Data type of the return value: BIGINT.

    • Example 

      SELECT bit_and(a) FROM testtable;

      The following result is returned:

      +------------+
| bit_and(a) |
+------------+
|          0 |
+------------+

    BIT_OR

    bit_or(x)

    • Description: This function returns the result of a bitwise OR operation on all bits of the input values.

    • Data type of the input value: BIGINT, DOUBLE, or FLOAT.

    • Data type of the return value: BIGINT.

    • Example 

      SELECT bit_or(a) FROM testtable;

      The following result is returned:

      +-----------+
| bit_or(a) |
+-----------+
|         3 |
+-----------+

    BIT_XOR

    bit_xor(x)

    • Description: This function returns the result of a bitwise XOR operation on all bits of the input values.

    • Data type of the input value: BIGINT, DOUBLE, or FLOAT.

    • Data type of the return value: BIGINT.

    • Example 

      SELECT bit_xor(a) FROM testtable;

      The following result is returned:

      +------------+
| bit_xor(a) |
+------------+
|          0 |
+------------+

    COUNT

    count([distinct|all] x)

    • Description: This function counts the number of records returned by a SELECT query.

      Note

      Description: distinct and all specify whether to remove duplicate records during counting. The default value is all, which indicates that all records are counted. If distinct is specified, only records that have unique values are counted.

    • Data type of the input value: NUMERIC, STRING, or BOOLEAN.

    • Data type of the return value: BIGINT.

    • Examples

      • Count the number of records whose values are unique in the a column of the testtable table.

        SELECT count(distinct a) FROM testtable;

        The following result is returned:

        +-------------------+
| count(distinct a) |
+-------------------+
|                 3 |
+-------------------+

      • Count the number of all records in the testtable table based on the values in the a column.

        SELECT count(all a) FROM testtable;

        The following result is returned:

        +--------------+
| count(all a) |
+--------------+
|            3 |
+--------------+

    MAX

    max(x)

    • Description: This function calculates the maximum value of a set of numeric values.

    • Data type of the input value: all data types. However, data of the BOOLEAN type is not included in the calculation.

      Note

      If a value in a specific column is NULL, the row that contains this value is not included in the calculation.

    • Data type of the return value: consistent with the data type of the input value of this function.

    • Example 

      SELECT max(a) FROM testtable;

      The following result is returned:

      +--------+
| max(a) |
+--------+
|      3 |
+--------+

    MIN

    min(value x)

    • Description: This function calculates the minimum value of a set of numeric values.

    • Data type of the input value: all data types. However, data of the BOOLEAN type is not included in the calculation.

      Note

      If a value in a specific column is NULL, the row that contains this value is not included in the calculation.

    • Data type of the return value: consistent with the data type of the input value of this function.

    • Example 

      SELECT min(a) FROM testtable;

      The following result is returned:

      +--------+
| min(a) |
+--------+
|      1 |
+--------+

    STD, STDDEV, or STDDEV_POP

    std(x)
stddev(x)
stddev_pop(x)

    • Description: This function returns the population standard deviation of all input values.

    • Data type of the input value: BIGINT or DOUBLE.

    • Data type of the return value: DOUBLE.

    • Examples

      • Example 1:

        SELECT std(a) FROM testtable;

        The following result is returned:

        +-------------------+
| std(a)            |
+-------------------+
| 0.816496580927726 |
+-------------------+

      • Example 2:

        SELECT stddev_pop(a) FROM testtable;

        The following result is returned:

        +-------------------+
| stddev_pop(a)     |
+-------------------+
| 0.816496580927726 |
+-------------------+

    STDDEV_SAMP

    stddev_samp(x)

    • Description: This function returns the sample standard deviation for a set of integers, decimals, or floating-point numbers.

    • Data type of the input value: BIGINT or DOUBLE.

    • Data type of the return value: DOUBLE.

    • Example 

      SELECT stddev_samp(a) FROM testtable;

      The following result is returned:

      +----------------+
| stddev_samp(a) |
+----------------+
|            1.0 |
+----------------+

    SUM

    sum(x)

    • Description: This function calculates the sum of all input values.

    • Data type of the input value: BIGINT, DOUBLE, or FLOAT.

    • Data type of the return value: BIGINT.

    • Example 

      SELECT sum(a) FROM testtable;

      The following result is returned:

      +--------+
| sum(a) |
+--------+
|      6 |
+--------+

    VARIANCE

    variance(x)

    • Description: This function returns the population standard variance for a set of integers, decimals, or floating-point numbers.

      Note

      • The VARIANCE() function ignores rows that have NULL values. If all values in a set are NULL, the VARIANCE() function directly returns NULL.

      • The VARIANCE() function is an extension to standard SQL and can be replaced by the standard SQL function VAR_POP().

    • Data type of the input value: BIGINT or DOUBLE.

    • Data type of the return value: DOUBLE.

    • Example 

      SELECT variance(a)  FROM testtable;
+----------------------------+
|         variance(a)        |
+----------------------------+
|    0.6666666666666666      |

    VAR_POP

    var_pop(x)

    • Description: This function returns the population standard variance for a set of integers, decimals, or floating-point numbers.

      Note

      • The VAR_POP() function ignores rows that have NULL values. If all values in a set are NULL, the VAR_POP() function directly returns NULL.

      • You can also use the VARIANCE() function, which is equivalent to the VAR_POP() function but is not a standard SQL function.

    • Data type of the input value: BIGINT or DOUBLE.

    • Data type of the return value: DOUBLE.

    • Example 

      SELECT var_pop(a) FROM testtable;

      The following result is returned:

      +--------------------+
| var_pop(a)         |
+--------------------+
| 0.6666666666666666 |
+--------------------+

    VAR_SAMP

    var_samp(x)

    • Description: This function returns the sample variance for a set of integers, decimals, or floating-point numbers.

    • Data type of the input value: BIGINT or DOUBLE.

    • Data type of the return value: DOUBLE.

    • Example 

      SELECT var_samp(a)  FROM testtable;

      The following result is returned:

      +-------------+
| var_samp(a) |
+-------------+
|         1.0 |
+-------------+

    GROUP_CONCAT

    GROUP_CONCAT([DISTINCT] col_name
             [ORDER BY col_name [ASC | DESC]]
             [SEPARATOR str_val])

    Clause

    Required

    Description

    DISTINCT

    No

    Specifies the columns from which you want to remove duplicates.

    ORDER BY

    Specifies the columns that you want to sort within a group. The following sorting methods are supported:

    • ASC: ascending order.

    • DESC: descending order.

    If you do not specify a sorting method, the ascending order is used.

    SEPARATOR

    Specifies the delimiter used to separate values within a group.

    If you do not specify a delimiter, a comma (,) is used.

    • Description: This function returns a string result of the concatenated values from a group. The values come from the returned results of the GROUP BY clause.

      Note

      NULL is returned only if all values in the columns that you want to concatenate by using the GROUP_CONCAT function are NULL.

    • Data type of the input value: STRING.

    • Data type of the return value: STRING.

    • Example:

      Create a table named person that uses the GROUP_CONCAT function.

      CREATE TABLE person(id INT,name VARCHAR,age INT ) DISTRIBUTED BY HASH(id);

      Insert data into the table.

      INSERT INTO person VALUES (1,'mary',13),(2,'eva',14),(2,'adam',13),(3,'eva',13),(3,null,13),(3,null,null),(4,null,13),(4,null,null);

      Group the person table by the id column. Use the GROUP_CONCAT function to display the distinct values of the name column with the same ID, sort the table in descending order by the name column, and then separate the name column values with number signs (#) in each group.

      SELECT
  id,
  GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#')
FROM person 
  GROUP BY id;

      The following result is returned:

      +------+--------------------------------------------------------------+
| id   | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') |
+------+--------------------------------------------------------------+
|    2 | eva#adam                                                     |
|    1 | mary                                                         |
|    4 | NULL                                                         |
|    3 | eva                                                          |
+------+--------------------------------------------------------------+