- Introduction
- Concepts
- Architecture
- Key Features
- Horizontal Scalability
- MySQL Compatible Syntax
- Replicate from and to MySQL
- Distributed Transactions with Strong Consistency
- Cloud Native Architecture
- Minimize ETL with HTAP
- Fault Tolerance & Recovery with Raft
- Automatic Rebalancing
- Deployment and Orchestration with Ansible, Kubernetes, Docker
- JSON Support
- Spark Integration
- Read Historical Data Without Restoring from Backup
- Fast Import and Restore of Data
- Hybrid of Column and Row Storage
- SQL Plan Management
- Open Source
- Online Schema Changes
- How-to
- Get Started
- Deploy
- Hardware Recommendations
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- Data Migration with Ansible
- Configure
- Secure
- Transport Layer Security (TLS)
- Generate Self-signed Certificates
- Monitor
- Migrate
- Maintain
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Data Types
- Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- Operators
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Miscellaneous Functions
- Precision Math
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
ROLLBACK
SELECT
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW SCHEMAS
SHOW STATUS
SHOW [FULL] TABLES
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
START TRANSACTION
TRACE
TRUNCATE
UPDATE
USE
- Constraints
- Generated Columns
- Character Set
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiSpark
- TiDB Binlog
- Tools
- Overview
- Use Cases
- Download
- Mydumper
- Syncer
- Loader
- TiDB Data Migration
- TiDB Lightning
- sync-diff-inspector
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- FAQs
- Support
- Contribute
- Adopters
- Releases
- All Releases
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v2.1).
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.
Name | Description |
---|---|
COUNT() | Return a count of the number of rows returned |
COUNT(DISTINCT) | Return the count of a number of different values |
SUM() | Return the sum |
AVG() | Return the average value of the argument |
MAX() | Return the maximum value |
MIN() | Return the minimum value |
GROUP_CONCAT() | Return a concatenated string |
- Unless otherwise stated, group functions ignore
NULL
values. - If you use a group function in a statement containing no
GROUP BY
clause, it is equivalent to grouping on all rows.
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 DISTINCT
and 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
SELECT
list - All columns referenced by the expression and belonging to the query's selected tables are elements of the
SELECT
list
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;
Unsupported aggregate functions
The following aggregate functions are currently unsupported in TiDB. You can track our progress in TiDB #7623:
STD
,STDDEV
,STDDEV_POP
STDDEV_SAMP
VARIANCE
,VAR_POP
VAR_SAMP
JSON_ARRAYAGG
JSON_OBJECTAGG