This topic describes the aggregate functions used in AnalyticDB for MySQL.

  • AVG: calculates the average value.
  • BIT_AND: returns the result of bitwise AND operations for all bits of the argument.
  • BIT_OR: returns the result of bitwise OR operations for all bits of the argument.
  • BIT_XOR: returns the result of bitwise XOR operations for all bits of the argument.
  • COUNT: calculates the number of records.
  • MAX: calculates the maximum value.
  • MIN: calculates the minimum value.
  • STD/STDDEV: returns the sample standard deviation of all input values.
  • STDDEV_POP: returns the population standard deviation of all input values.
  • STDDEV_SAMP: returns the population standard deviation for a group of integers, decimals, or floating-point numbers.
  • SUM: calculates the sum of all input values.
  • VAR_POP: returns the population variance for a group of integers, decimals, or floating-point numbers.
  • VAR_SAMP: returns the sample variance for a group of integers, decimals, or floating-point numbers.
  • VARIANCE: returns the population variance for a group of integers, decimals, or floating-point numbers.

The aggregate functions in this topic use the testtable table as test data. 

create table testtable(a int) distributed by hash(a);           
insert into testtable values (1),(2),(3);

AVG

avg(bigint x)
avg(double x)
avg(float x)
  • Description: This function calculates the average value.
  • Return value type: DOUBLE.
  • Example: 
    select avg(a) from testtable;
+--------+
| avg(a) |
+--------+
|    2.0 |

BIT_AND

bit_and(float x)
bit_and(bigint x)
bit_and(double x)
  • Description: This function returns the result of bitwise AND operations for all bits of the argument.
  • Return value type: BIGINT.
  • Example: 
    select bit_and(a) from testtable;
+------------+
| bit_and(a) |
+------------+
|          0 |

BIT_OR

bit_or(float x)
bit_or(bigint x)
bit_or(double x)
  • Description: This function returns the result of bitwise OR operations for all bits of the argument.
  • Return value type: BIGINT.
  • Example: 
    select bit_or(a) from testtable;
+----------------------------+
|          bit_or(a)         |
+----------------------------+
|           3                |

BIT_XOR

bit_xor(double x)
bit_xor(bigint x)
bit_xor(float x)
  • Description: This function returns the result of bitwise XOR operations for all bits of the argument.
  • Return value type: BIGINT.
  • Example: 
    select bit_xor(a) from testtable;
+----------------------------+
|          bit_xor(a)        |
+----------------------------+
|          0                 |

COUNT

count([distinct|all] value x)
  • Description: This function counts the number of records.

    distinct and all specify whether to exclude duplicate records in the counting process. The default value is all, which indicates that all records are counted. If distinct is specified, only records with distinct values are counted.

  • Return value type: BIGINT.
  • Example: 
    select count(distinct a) from testtable;
+-------------------+
| count(DISTINCT a) |
+-------------------+
|                 3 |

MAX

max(value x)
  • Description: This function calculates the maximum value.

    value can be of any data type except BOOLEAN.

    If the value for a row in the specified column is null, this row is ignored.

  • Return value type: LONG.
  • Example: 
    select max(a) from testtable;
+--------+
| max(a) |
+--------+
|      3 |

MIN

min(value x)
  • Description: This function calculates the minimum value.

    value can be of any data type except BOOLEAN.

    If the value for a row in the specified column is null, this row is ignored.

  • Return value type: LONG.
  • Example: 
    select min(a) from testtable;
+--------+
| min(a) |
+--------+
|      1 |

STD/STDDEV

std(double x)
std(bigint x)
stddev(double x)
stddev(bigint x)
  • Description: This function returns the sample standard deviation of all input values.
  • Return value type: DOUBLE.
  • Example: 
    select std(a) from testtable;
+-------------------+
| std(a)            |
+-------------------+
| 0.816496580927726 |

STDDEV_POP

stddev_pop(double x)
stddev_pop(bigint x)
  • Description: This function returns the population standard deviation of all input values.
  • Return value type: DOUBLE.
  • Example: 
    select stddev_pop(a) from testtable;
+----------------------------+
|    stddev_pop(a)           |
+----------------------------+
|    0.816496580927726       |

STDDEV_SAMP

stddev_samp(double x)
stddev_samp(bigint x)
  • Description: This function returns the population standard deviation for a group of integers, decimals, or floating-point numbers.
  • Return value type: DOUBLE.
  • Example: 
    select stddev_samp(a) from testtable;
+----------------------------+
|     stddev_samp(a)         |
+----------------------------+
|         1.0                |

SUM

sum(double x)
sum(float x)
sum(bigint x)
  • Description: This function calculates the sum of all input values.
  • Return value type: BIGINT.
  • Example: 
    select sum(a) from testtable;
+----------------------------+
|         sum(a)             |
+----------------------------+
|          6                 |

VAR_POP

var_pop(double x)
var_pop(bigint x)
  • Description: This function returns the population variance for a group of integers, decimals, or floating-point numbers. You can also use the VARIANCE() function, which has the same meaning as the VAR_POP function. However, the VARIANCE() function is not a standard SQL function. If no matches are found, VAR_POP() returns the value NULL.
  • Return value type: DOUBLE.
  • Example: 
    select var_pop(a)  from testtable;
+----------------------------+
|         var_pop(a)         |
+----------------------------+
|   0.6666666666666666       |

VAR_SAMP

var_samp(double x)
var_samp(bigint x)
  • Description: This function returns the sample variance for a group of integers, decimals, or floating-point numbers.
  • Return value type: DOUBLE.
  • Example: 
    select var_samp(a)  from testtable;
+----------------------------+
|         var_samp(a)        |
+----------------------------+
|            1.0             |

VARIANCE

variance(double x)
variance(bigint x)
  • Description: This function returns the population standard variance for a group of integers, decimals, or floating-point numbers. The VARIANCE() function is an extension to standard SQL and can be replaced by the standard SQL function VAR_POP(). If no matches are found, VARIANCE() returns the value NULL.
  • Return value type: DOUBLE.
  • Example: 
    select variance(a)  from testtable;
+----------------------------+
|         variance(a)        |
+----------------------------+
|    0.6666666666666666      |