Summarize Datasets with SQL Aggregate Functions - AnalyticDB for MySQL

Aggregate functions compute a single result from a set of input values. This page covers the aggregate functions supported in AnalyticDB for MySQL, including syntax, input and return types, and runnable examples.

Functions covered: ARBITRARY, AVG, BIT_AND, BIT_OR, BIT_XOR, COUNT, MAX, MIN, STD / STDDEV / STDDEV_POP, STDDEV_SAMP, SUM, VARIANCE / VAR_POP, VAR_SAMP, GROUP_CONCAT

Usage notes

NULL handling

Most aggregate functions ignore NULL values. For MAX and MIN, rows where the input column is NULL are excluded from the calculation. For VARIANCE, VAR_POP, and their aliases, if all values in the input set are NULL, the function returns NULL.

DISTINCT modifier

Pass DISTINCT to count or aggregate only unique values. For example, COUNT(DISTINCT a) counts distinct non-NULL values in column a.

Test table used in examples

All examples except GROUP_CONCAT use a table named testtable, created and populated as follows:

CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);
INSERT INTO testtable VALUES (1),(2),(3);

General aggregate functions

ARBITRARY

arbitrary(x)

Returns a single value from the input set. The value returned is non-deterministic.

Input types	All data types
Return type	Same as input

Example

SELECT arbitrary(a) FROM testtable;

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

AVG

avg(x)

Returns the arithmetic mean of the input values.

Input types	BIGINT, DOUBLE, FLOAT
Return type	DOUBLE

Example

SELECT avg(a) FROM testtable;

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

COUNT

count([DISTINCT | ALL] x)

Counts the number of rows in the result set.

ALL (default): counts all rows, including duplicates.
DISTINCT: counts only rows with unique values.

Input types	NUMERIC, STRING, BOOLEAN
Return type	BIGINT

Examples

Count distinct values in column a:

SELECT count(DISTINCT a) FROM testtable;

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

Count all rows in column a:

SELECT count(ALL a) FROM testtable;

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

MAX

max(x)

Returns the maximum value in the input set. BOOLEAN values and NULL rows are excluded.

Input types	All data types (BOOLEAN excluded)
Return type	Same as input

Example

SELECT max(a) FROM testtable;

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

MIN

min(x)

Returns the minimum value in the input set. BOOLEAN values and NULL rows are excluded.

Input types	All data types (BOOLEAN excluded)
Return type	Same as input

Example

SELECT min(a) FROM testtable;

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

SUM

sum(x)

Returns the sum of all input values.

Input types	BIGINT, DOUBLE, FLOAT
Return type	BIGINT

Example

SELECT sum(a) FROM testtable;

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

GROUP_CONCAT

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

Concatenates values from a group into a single string, using the results of a GROUP BY clause. Returns NULL only if all values being concatenated are NULL.

Clause	Required	Description
`DISTINCT`	No	Removes duplicate values before concatenating
`ORDER BY`	No	Sorts values within the group before concatenating. Defaults to ascending order
`SEPARATOR`	No	Sets the delimiter between values. Defaults to a comma (`,`)

Input type	STRING
Return type	STRING

Example

Create and populate the person table:

CREATE TABLE person(id INT, name VARCHAR, age INT) DISTRIBUTED BY HASH(id);
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 by id, concatenate distinct names in descending order, separated by #:

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

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

Bitwise aggregate functions

BIT_AND

bit_and(x)

Returns the result of a bitwise AND operation across all input values.

Input types	BIGINT, DOUBLE, FLOAT
Return type	BIGINT

Example

SELECT bit_and(a) FROM testtable;

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

BIT_OR

bit_or(x)

Returns the result of a bitwise OR operation across all input values.

Input types	BIGINT, DOUBLE, FLOAT
Return type	BIGINT

Example

SELECT bit_or(a) FROM testtable;

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

BIT_XOR

bit_xor(x)

Returns the result of a bitwise XOR operation across all input values.

Input types	BIGINT, DOUBLE, FLOAT
Return type	BIGINT

Example

SELECT bit_xor(a) FROM testtable;

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

Statistical aggregate functions

STD / STDDEV / STDDEV_POP

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

Returns the population standard deviation of the input values. STD, STDDEV, and STDDEV_POP are aliases for the same function.

Input types	BIGINT, DOUBLE
Return type	DOUBLE
Aliases	`std`, `stddev`, `stddev_pop`

Examples

SELECT std(a) FROM testtable;

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

SELECT stddev_pop(a) FROM testtable;

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

STDDEV_SAMP

stddev_samp(x)

Returns the sample standard deviation of the input values.

Input types	BIGINT, DOUBLE
Return type	DOUBLE

Example

SELECT stddev_samp(a) FROM testtable;

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

VARIANCE / VAR_POP

variance(x)
var_pop(x)

Returns the population standard variance of the input values. Rows with NULL values are ignored; if all values are NULL, the function returns NULL.

VARIANCE is a MySQL extension to standard SQL. VAR_POP is the standard SQL equivalent. Both functions produce identical results.

Input types	BIGINT, DOUBLE
Return type	DOUBLE
Aliases	`variance`, `var_pop`

Examples

SELECT variance(a) FROM testtable;

+--------------------+
| variance(a)        |
+--------------------+
| 0.6666666666666666 |
+--------------------+

SELECT var_pop(a) FROM testtable;

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

VAR_SAMP

var_samp(x)

Returns the sample variance of the input values.

Input types	BIGINT, DOUBLE
Return type	DOUBLE

Example

SELECT var_samp(a) FROM testtable;

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