12.19.1 GROUP BY (Aggregate) Functions

Table 12.24 Aggregate (GROUP BY) Functions

NameDescription
AVG()Return the average value of the argument
BIT_AND()Return bitwise and
BIT_OR()Return bitwise or
BIT_XOR()Return bitwise xor
COUNT(DISTINCT)Return the count of a number of different values
COUNT()Return a count of the number of rows returned
GROUP_CONCAT()Return a concatenated string
MAX()Return the maximum value
MIN()Return the minimum value
STD()Return the population standard deviation
STDDEV_POP()Return the population standard deviation
STDDEV_SAMP()Return the sample standard deviation
STDDEV()Return the population 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

This section describes group (aggregate) functions that operate on sets of values. 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. For more information, see Section 12.19.3, “MySQL Extensions to 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.