PingCAP
  • Cloud
  • TiDB Academy
  • Docs
  • Success Stories
  • Blog
  • Free Consultation
PingCAP
  • Cloud
  • TiDB Academy
  • Docs
  • Success Stories
  • Blog
  • Free Consultation

Contact

中文
Documentation
  • About TiDB
    • TiDB Introduction
    • TiDB Architecture
  • Quick Start
    • TiDB Quick Start Guide
    • Basic SQL Statements
    • Bikeshare Example Database
  • TiDB User Guide
    • TiDB Server Administration
      • The TiDB Server
      • The TiDB Command Options
      • The TiDB Data Directory
      • The TiDB System Database
      • The TiDB System Variables
      • The TiDB Specific System Variables
      • The TiDB Server Logs
      • The TiDB Access Privilege System
      • TiDB User Account Management
      • Use Encrypted Connections
    • SQL Optimization and Execution
      • SQL Optimization Process
      • Understand the Query Execution Plan
      • Introduction to Statistics
    • Language Structure
      • Literal Values
      • Schema Object Names
      • Keywords and Reserved Words
      • User-Defined Variables
      • Expression Syntax
      • Comment Syntax
    • Globalization
      • Character Set Support
      • Character Set Configuration
      • Time Zone Support
    • Data Types
      • Numeric Types
      • Date and Time Types
      • String Types
      • JSON Types
      • The ENUM data type
      • The SET Type
      • Data Type Default Values
    • 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 Statement Syntax
      • Data Definition Statements
      • Data Manipulation Statements
      • Transactions
      • Database Administration Statements
      • Prepared SQL Statement Syntax
      • Utility Statements
      • TiDB SQL Syntax Diagram
    • Generated Columns
    • Connectors and APIs
    • TiDB Transaction Isolation Levels
    • Error Codes and Troubleshooting
    • Compatibility with MySQL
    • TiDB Memory Control
    • Slow Query Log
    • Advanced Usage
      • Read Data From History Versions
      • Garbage Collection (GC)
  • TiDB Operations Guide
    • Hardware and Software Requirements
    • Deploy
      • Ansible Deployment (Recommended)
      • Offline Deployment Using Ansible
      • Docker Deployment
      • Docker Compose Deployment
      • Cross-DC Deployment Solutions
      • Kubernetes Deployment
    • Configure
      • Configuration Flags
      • Configuration File Description
      • Modify Component Configuration Using Ansible
      • Enable TLS Authentication
      • Generate Self-signed Certificates
      • Cluster Topology Configuration
    • Monitor
      • Monitoring Framework Overview
      • Key Monitoring Metrics
        • Overview
        • TiDB
        • PD
        • TiKV
      • Monitor a TiDB Cluster
    • Scale
      • Scale a TiDB Cluster
      • Scale Using Ansible
    • Upgrade
      • Upgrade the Component Version
      • TiDB 2.0 Upgrade Guide
      • TiDB 2.1 Upgrade Guide
    • Tune Performance
    • Backup and Migrate
      • Backup and Restore
      • Migrate
        • Migration Overview
        • Migrate All the Data
        • Migrate the Data Incrementally
    • TiDB-Ansible Common Operations
    • Troubleshoot
  • TiDB Enterprise Tools
    • Syncer
    • mydumper
    • Loader
    • Data Migration
      • Overview
      • Restrictions
      • Deploy
      • Features
        • Table Routing
        • Black and White Lists
        • Binlog Event Filtering
        • Column Mapping
        • Synchronization Delay Monitoring
        • Sharding Support
          • Introduction
          • Restrictions
          • Handle Sharding DDL Locks Manually
      • Usage Scenarios
        • Simple Scenario
        • Shard Merge Scenario
      • Configure
        • Overview
        • Task Configuration
      • Monitor
      • Manage the Task
      • Cluster Operations
      • Troubleshoot
    • TiDB-Lightning
      • Overview
      • Deployment
      • Checkpoints
      • Table Filter
      • Monitor
      • Troubleshooting
      • FAQs
    • TiDB-Binlog
    • PD Control
    • PD Recover
    • TiKV Control
    • TiDB Controller
    • Download
  • TiKV Documentation
  • TiSpark Documentation
    • Quick Start Guide
    • User Guide
  • Frequently Asked Questions (FAQ)
  • TiDB Best Practices
  • Releases
    • 2.1.4
    • 2.1.3
    • 3.0 Beta
    • 2.0.11
    • 2.1.2
    • 2.0.10
    • 2.1.1
    • 2.1 GA
    • 2.0.9
    • 2.1 RC5
    • 2.1 RC4
    • 2.0.8
    • 2.1 RC3
    • 2.1 RC2
    • 2.0.7
    • 2.1 RC1
    • 2.0.6
    • 2.0.5
    • 2.1 Beta
    • 2.0.4
    • 2.0.3
    • 2.0.2
    • 2.0.1
    • 2.0
    • 2.0 RC5
    • 2.0 RC4
    • 2.0 RC3
    • 2.0 RC1
    • 1.1 Beta
    • 1.0.8
    • 1.0.7
    • 1.1 Alpha
    • 1.0.6
    • 1.0.5
    • 1.0.4
    • 1.0.3
    • 1.0.2
    • 1.0.1
    • 1.0
    • Pre-GA
    • RC4
    • RC3
    • RC2
    • RC1
  • TiDB Adopters
  • TiDB Roadmap
  • Connect with us
  • More Resources
    • PingCAP Blog
    • Weekly Update

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 (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 does not currently enable 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
"Aggregate (GROUP BY) Functions" was last updated Oct 26 2018: sql: fix a broken link (#689) (b2430a1)
Improve this page

What’s on this page

Product

  • TiDB
  • TiSpark
  • Roadmap

Docs

  • Quick Start
  • Best Practices
  • FAQ
  • TiDB Utilities
  • Release Notes

Resources

  • Blog
  • Weekly
  • GitHub
  • TiDB Community

Company

  • About
  • Careers
  • News
  • Contact Us
  • Privacy Policy
  • Terms of Service

Connect

  • Twitter
  • LinkedIn
  • Reddit
  • Google Group
  • Stack Overflow

© 2018 PingCAP. All Rights Reserved.

中文