Aggregate (GROUP BY) Functions
This document describes details about the supported aggregate functions in TiDB.
Supported aggregate functions
This section describes the supported MySQL
GROUP BY aggregate functions in TiDB.
|Return a count of the number of rows returned|
|Return the count of a number of different values|
|Return the sum|
|Return the average value of the argument|
|Return the maximum value|
|Return the minimum value|
|Return a concatenated string|
|Return the population standard variance|
|Return the population standard deviation|
|Return the sample variance|
|Return the sample standard deviation|
|Return the result set as a single JSON object containing key-value pairs|
- Unless otherwise stated, group functions ignore
- If you use a group function in a statement containing no
GROUP BYclause, it is equivalent to grouping on all rows.
In addition, TiDB also provides the following aggregate functions:
This function returns the percentile of
constant_integer_exprargument indicates the percentage value which is a constant integer in the range of
[1,100]. A percentile Pk (
krepresents percentage) indicates that there are at least
k%values in the data set that are less than or equal to Pk.
This function only supports the numeric type and the date and time type as the returned type of
expr. For other returned types,
The following example shows how to calculate the fiftieth percentile of a
drop table if exists t; create table t(a int); insert into t values(1), (2), (3);
select approx_percentile(a, 50) from t;
+--------------------------+ | approx_percentile(a, 50) | +--------------------------+ | 2 | +--------------------------+ 1 row in set (0.00 sec)
Except for the
APPROX_PERCENTILE() functions, all the preceding functions can serve as Window functions.
GROUP BY modifiers
TiDB does not currently support
GROUP BY modifiers such as
WITH ROLLUP. We plan to add support in the future. See TiDB #4250.
SQL mode support
TiDB supports the SQL Mode
ONLY_FULL_GROUP_BY, and when enabled TiDB will refuse queries with ambiguous non-aggregated columns. For example, this query is illegal with
ONLY_FULL_GROUP_BY enabled because the non-aggregated column "b" in the
SELECT list does not appear in the
GROUP BY statement:
drop table if exists t; create table t(a bigint, b bigint, c bigint); insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3); mysql> select a, b, sum(c) from t group by a; +------+------+--------+ | a | b | sum(c) | +------+------+--------+ | 1 | 2 | 3 | | 2 | 2 | 3 | | 3 | 2 | 3 | +------+------+--------+ 3 rows in set (0.01 sec) mysql> set sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select a, b, sum(c) from t group by a; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
TiDB currently enables the
ONLY_FULL_GROUP_BY mode by default.
Differences from MySQL
The current implementation of
ONLY_FULL_GROUP_BY is less strict than that in MySQL 5.7. For example, suppose that we execute the following query, expecting the results to be ordered by "c":
drop table if exists t; create table t(a bigint, b bigint, c bigint); insert into t values(1, 2, 1), (1, 2, 2), (1, 3, 1), (1, 3, 2); select distinct a, b from t order by c;
To order the result, duplicates must be eliminated first. But to do so, which row should we keep? This choice influences the retained value of "c", which in turn influences ordering and makes it arbitrary as well.
In MySQL, a query that has
ORDER BY is rejected as invalid if any
ORDER BY expression does not satisfy at least one of these conditions:
- The expression is equal to one in the
- All columns referenced by the expression and belonging to the query's selected tables are elements of the
But in TiDB, the above query is legal, for more information see #4254.
Another TiDB extension to standard SQL permits references in the
HAVING clause to aliased expressions in the
SELECT list. For example, the following query returns "name" values that occur only once in table "orders":
select name, count(name) from orders group by name having count(name) = 1;
The TiDB extension permits the use of an alias in the
HAVING clause for the aggregated column:
select name, count(name) as c from orders group by name having c = 1;
Standard SQL permits only column expressions in
GROUP BY clauses, so a statement such as this is invalid because "FLOOR(value/100)" is a noncolumn expression:
select id, floor(value/100) from tbl_name group by id, floor(value/100);
TiDB extends standard SQL to permit noncolumn expressions in
GROUP BY clauses and considers the preceding statement valid.
Standard SQL also does not permit aliases in
GROUP BY clauses. TiDB extends standard SQL to permit aliases, so another way to write the query is as follows:
select id, floor(value/100) as val from tbl_name group by id, val;
Related system variables
group_concat_max_len variable sets the maximum number of items for the