This section describes aggregate functions that operate on sets
of values. They are often used with a GROUP
BY
clause to group values into subsets.
Table 12.25 Aggregate Functions
Name | Description | Introduced |
---|---|---|
AVG() |
Return the average value of the argument | |
BIT_AND() |
Return bitwise AND | |
BIT_OR() |
Return bitwise OR | |
BIT_XOR() |
Return bitwise XOR | |
COUNT() |
Return a count of the number of rows returned | |
COUNT(DISTINCT) |
Return the count of a number of different values | |
GROUP_CONCAT() |
Return a concatenated string | |
JSON_ARRAYAGG() |
Return result set as a single JSON array | 5.7.22 |
JSON_OBJECTAGG() |
Return result set as a single JSON object | 5.7.22 |
MAX() |
Return the maximum value | |
MIN() |
Return the minimum value | |
STD() |
Return the population standard deviation | |
STDDEV() |
Return the population standard deviation | |
STDDEV_POP() |
Return the population standard deviation | |
STDDEV_SAMP() |
Return the sample standard deviation | |
SUM() |
Return the sum | |
VAR_POP() |
Return the population standard variance | |
VAR_SAMP() |
Return the sample variance | |
VARIANCE() |
Return the population standard variance |
Unless otherwise stated, aggregate functions ignore
NULL
values.
If you use an aggregate function in a statement containing no
GROUP BY
clause, it is equivalent to grouping
on all rows. For more information, see
Section 12.19.3, “MySQL Handling of GROUP BY”.
For numeric arguments, the variance and standard deviation
functions return a DOUBLE
value.
The SUM()
and
AVG()
functions return a
DECIMAL
value for exact-value
arguments (integer or DECIMAL
),
and a DOUBLE
value for
approximate-value arguments
(FLOAT
or
DOUBLE
).
The SUM()
and
AVG()
aggregate functions do not
work with temporal values. (They convert the values to numbers,
losing everything after the first nonnumeric character.) To work
around this problem, convert to numeric units, perform the
aggregate operation, and convert back to a temporal value.
Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
Functions such as SUM()
or
AVG()
that expect a numeric
argument cast the argument to a number if necessary. For
SET
or
ENUM
values, the cast operation
causes the underlying numeric value to be used.
The BIT_AND()
,
BIT_OR()
, and
BIT_XOR()
aggregate functions
perform bit operations. They require
BIGINT
(64-bit integer) arguments
and return BIGINT
values.
Arguments of other types are converted to
BIGINT
and truncation might
occur. For information about a change in MySQL 8.0 that permits
bit operations to take binary string type arguments
(BINARY
,
VARBINARY
, and the
BLOB
types), see
Section 12.12, “Bit Functions and Operators”.
Returns the average value of
. Theexpr
DISTINCT
option can be used to return the average of the distinct values ofexpr
.If there are no matching rows,
AVG()
returnsNULL
.mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;
Returns the bitwise
AND
of all bits inexpr
. The calculation is performed with 64-bit (BIGINT
) precision.If there are no matching rows,
BIT_AND()
returns a neutral value (all bits set to 1).Returns the bitwise
OR
of all bits inexpr
. The calculation is performed with 64-bit (BIGINT
) precision.If there are no matching rows,
BIT_OR()
returns a neutral value (all bits set to 0).Returns the bitwise
XOR
of all bits inexpr
. The calculation is performed with 64-bit (BIGINT
) precision.If there are no matching rows,
BIT_XOR()
returns a neutral value (all bits set to 0).Returns a count of the number of non-
NULL
values ofexpr
in the rows retrieved by aSELECT
statement. The result is aBIGINT
value.If there are no matching rows,
COUNT()
returns0
.mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
COUNT(*)
is somewhat different in that it returns a count of the number of rows retrieved, whether or not they containNULL
values.For transactional storage engines such as
InnoDB
, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.InnoDB
does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently,SELECT COUNT(*)
statements only count rows visible to the current transaction.Prior to MySQL 5.7.18,
InnoDB
processesSELECT COUNT(*)
statements by scanning the clustered index. As of MySQL 5.7.18,InnoDB
processesSELECT COUNT(*)
statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.Processing
SELECT COUNT(*)
statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, useSHOW TABLE STATUS
.InnoDB
handlesSELECT COUNT(*)
andSELECT COUNT(1)
operations in the same way. There is no performance difference.For
MyISAM
tables,COUNT(*)
is optimized to return very quickly if theSELECT
retrieves from one table, no other columns are retrieved, and there is noWHERE
clause. For example:mysql> SELECT COUNT(*) FROM student;
This optimization only applies to
MyISAM
tables, because an exact row count is stored for this storage engine and can be accessed very quickly.COUNT(1)
is only subject to the same optimization if the first column is defined asNOT NULL
.COUNT(DISTINCT
expr
,[expr
...])Returns a count of the number of rows with different non-
NULL
expr
values.If there are no matching rows,
COUNT(DISTINCT)
returns0
.mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression combinations that do not contain
NULL
by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions insideCOUNT(DISTINCT ...)
.This function returns a string result with the concatenated non-
NULL
values from a group. It returnsNULL
if there are no non-NULL
values. The full syntax is as follows:GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
mysql> SELECT student_name, GROUP_CONCAT(test_score) FROM student GROUP BY student_name;
Or:
mysql> SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name;
In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the
DISTINCT
clause. To sort values in the result, use theORDER BY
clause. To sort in reverse order, add theDESC
(descending) keyword to the name of the column you are sorting by in theORDER BY
clause. The default is ascending order; this may be specified explicitly using theASC
keyword. The default separator between values in a group is comma (,
). To specify a separator explicitly, useSEPARATOR
followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specifySEPARATOR ''
.The result is truncated to the maximum length that is given by the
group_concat_max_len
system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value ofmax_allowed_packet
. The syntax to change the value ofgroup_concat_max_len
at runtime is as follows, whereval
is an unsigned integer:SET [GLOBAL | SESSION] group_concat_max_len = val;
The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is
TEXT
orBLOB
unlessgroup_concat_max_len
is less than or equal to 512, in which case the result type isVARCHAR
orVARBINARY
.If
GROUP_CONCAT()
is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the--binary-as-hex
. For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.See also
CONCAT()
andCONCAT_WS()
: Section 12.8, “String Functions and Operators”.Aggregates a result set as a single
JSON
array whose elements consist of the rows. The order of elements in this array is undefined. The function acts on a column or an expression that evaluates to a single value. ReturnsNULL
if the result contains no rows, or in the event of an error.mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ | 2 | color | red | | 2 | fabric | silk | | 3 | color | green | | 3 | shape | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes -> FROM t3 GROUP BY o_id; +------+---------------------+ | o_id | attributes | +------+---------------------+ | 2 | ["color", "fabric"] | | 3 | ["color", "shape"] | +------+---------------------+ 2 rows in set (0.00 sec)
Added in MySQL 5.7.22.
Takes two column names or expressions as arguments, the first of these being used as a key and the second as a value, and returns a JSON object containing key-value pairs. Returns
NULL
if the result contains no rows, or in the event of an error. An error occurs if any key name isNULL
or the number of arguments is not equal to 2.mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ | 2 | color | red | | 2 | fabric | silk | | 3 | color | green | | 3 | shape | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) -> FROM t3 GROUP BY o_id; +------+---------------------------------------+ | o_id | JSON_OBJECTAGG(attribute, value) | +------+---------------------------------------+ | 2 | {"color": "red", "fabric": "silk"} | | 3 | {"color": "green", "shape": "square"} | +------+---------------------------------------+ 2 rows in set (0.00 sec)
Duplicate key handling. When the result of this function is normalized, values having duplicate keys are discarded. In keeping with the MySQL
JSON
data type specification that does not permit duplicate keys, only the last value encountered is used with that key in the returned object (“last duplicate key wins”). This means that the result of using this function on columns from aSELECT
can depend on the order in which the rows are returned, which is not guaranteed.Consider the following:
mysql> CREATE TABLE t(c VARCHAR(10), i INT); Query OK, 0 rows affected (0.33 sec) mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5); Query OK, 3 rows affected (0.10 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT c, i FROM t; +------+------+ | c | i | +------+------+ | key | 3 | | key | 4 | | key | 5 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT JSON_OBJECTAGG(c, i) FROM t; +----------------------+ | JSON_OBJECTAGG(c, i) | +----------------------+ | {"key": 5} | +----------------------+ 1 row in set (0.00 sec) mysql> DELETE FROM t; Query OK, 3 rows affected (0.08 sec) mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT c, i FROM t; +------+------+ | c | i | +------+------+ | key | 3 | | key | 5 | | key | 4 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT JSON_OBJECTAGG(c, i) FROM t; +----------------------+ | JSON_OBJECTAGG(c, i) | +----------------------+ | {"key": 4} | +----------------------+ 1 row in set (0.00 sec)
See Normalization, Merging, and Autowrapping of JSON Values, for additional information and examples.
Added in MySQL 5.7.22.
Returns the maximum value of
expr
.MAX()
may take a string argument; in such cases, it returns the maximum string value. See Section 8.3.1, “How MySQL Uses Indexes”. TheDISTINCT
keyword can be used to find the maximum of the distinct values ofexpr
, however, this produces the same result as omittingDISTINCT
.If there are no matching rows,
MAX()
returnsNULL
.mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
For
MAX()
, MySQL currently comparesENUM
andSET
columns by their string value rather than by the string's relative position in the set. This differs from howORDER BY
compares them.Returns the minimum value of
expr
.MIN()
may take a string argument; in such cases, it returns the minimum string value. See Section 8.3.1, “How MySQL Uses Indexes”. TheDISTINCT
keyword can be used to find the minimum of the distinct values ofexpr
, however, this produces the same result as omittingDISTINCT
.If there are no matching rows,
MIN()
returnsNULL
.mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
For
MIN()
, MySQL currently comparesENUM
andSET
columns by their string value rather than by the string's relative position in the set. This differs from howORDER BY
compares them.Returns the population standard deviation of
expr
.STD()
is a synonym for the standard SQL functionSTDDEV_POP()
, provided as a MySQL extension.If there are no matching rows,
STD()
returnsNULL
.Returns the population standard deviation of
expr
.STDDEV()
is a synonym for the standard SQL functionSTDDEV_POP()
, provided for compatibility with Oracle.If there are no matching rows,
STDDEV()
returnsNULL
.Returns the population standard deviation of
expr
(the square root ofVAR_POP()
). You can also useSTD()
orSTDDEV()
, which are equivalent but not standard SQL.If there are no matching rows,
STDDEV_POP()
returnsNULL
.Returns the sample standard deviation of
expr
(the square root ofVAR_SAMP()
.If there are no matching rows,
STDDEV_SAMP()
returnsNULL
.Returns the sum of
expr
. If the return set has no rows,SUM()
returnsNULL
. TheDISTINCT
keyword can be used to sum only the distinct values ofexpr
.If there are no matching rows,
SUM()
returnsNULL
.Returns the population standard variance of
expr
. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also useVARIANCE()
, which is equivalent but is not standard SQL.If there are no matching rows,
VAR_POP()
returnsNULL
.Returns the sample variance of
expr
. That is, the denominator is the number of rows minus one.If there are no matching rows,
VAR_SAMP()
returnsNULL
.Returns the population standard variance of
expr
.VARIANCE()
is a synonym for the standard SQL functionVAR_POP()
, provided as a MySQL extension.If there are no matching rows,
VARIANCE()
returnsNULL
.