Table 12.21. Aggregate (GROUP BY
)Functions
Name  Description 

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.17.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 exactvalue arguments (integer or DECIMAL
), and a DOUBLE
value for approximatevalue 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
))) FROMtbl_name
;SELECT FROM_DAYS(SUM(TO_DAYS(date_col
))) FROMtbl_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.
Returns the average value of
. The expr
DISTINCT
option can be used to return the average of the distinct values of expr
.
AVG()
returns
NULL
if there were no matching rows.
mysql>SELECT student_name, AVG(test_score)
>FROM student
>GROUP BY student_name;
Returns the bitwise AND
of all bits in expr
.
The calculation is performed with 64bit (BIGINT
) precision.
This function returns 18446744073709551615
if there were no matching
rows. (This is the value of an unsigned BIGINT
value with all bits set to 1.)
Returns the bitwise OR
of all bits in expr
.
The calculation is performed with 64bit (BIGINT
) precision.
This function returns 0
if there were no matching rows.
Returns the bitwise XOR
of all bits in expr
.
The calculation is performed with 64bit (BIGINT
) precision.
This function returns 0
if there were no matching rows.
Returns a count of the number of nonNULL
values of expr
in the rows retrieved by a SELECT
statement. The result is a BIGINT
value.
COUNT()
returns
0
if there were no matching rows.
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
contain NULL
values.
COUNT(*)
is
optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved, and there is no WHERE
clause. For example:
mysql> SELECT COUNT(*) FROM
student;
This optimization applies only to MyISAM
tables only, because an exact
row count is stored for this storage engine and can be accessed very quickly. For transactional
storage engines such as InnoDB
, storing an exact row count is more
problematic because multiple transactions may be occurring, each of which may affect the count.
COUNT(DISTINCT
expr
,[expr
...])
Returns a count of the number of rows with different nonNULL
expr
values.
COUNT(DISTINCT)
returns 0
if there were no matching rows.
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 inside COUNT(DISTINCT ...)
.
This function returns a string result with the concatenated nonNULL
values from a group. It returns NULL
if there are no nonNULL
values. The full syntax is as follows:
GROUP_CONCAT([DISTINCT]expr
[,expr
...] [ORDER BY {unsigned_integer
col_name
expr
} [ASC  DESC] [,col_name
...]] [SEPARATORstr_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 the
ORDER BY
clause. To sort in reverse order, add the DESC
(descending) keyword to the name of the column you are sorting
by in the ORDER BY
clause. The default is ascending order; this may be
specified explicitly using the ASC
keyword. The default separator
between values in a group is comma (",
"). To specify a separator explicitly, use SEPARATOR
followed by the string literal value that should be
inserted between group values. To eliminate the separator altogether, specify SEPARATOR
''
.
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 of max_allowed_packet
. The syntax to change the value of group_concat_max_len
at runtime is as follows, where val
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
or BLOB
unless group_concat_max_len
is less than or equal to 512, in which case
the result type is VARCHAR
or VARBINARY
.
See also CONCAT()
and CONCAT_WS()
: Section 12.5,
"String Functions".
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". The DISTINCT
keyword can be used to find the maximum
of the distinct values of expr
, however, this produces the
same result as omitting DISTINCT
.
MAX()
returns
NULL
if there were no matching rows.
mysql>SELECT student_name, MIN(test_score), MAX(test_score)
>FROM student
>GROUP BY student_name;
For MAX()
, MySQL
currently compares ENUM
and SET
columns by their string value rather than by the string's relative position in the set. This differs
from how ORDER BY
compares them. This is expected to be rectified in a
future MySQL release.
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". The DISTINCT
keyword can be used to find the minimum
of the distinct values of expr
, however, this produces the
same result as omitting DISTINCT
.
MIN()
returns
NULL
if there were no matching rows.
mysql>SELECT student_name, MIN(test_score), MAX(test_score)
>FROM student
>GROUP BY student_name;
For MIN()
, MySQL
currently compares ENUM
and SET
columns by their string value rather than by the string's relative position in the set. This differs
from how ORDER BY
compares them. This is expected to be rectified in a
future MySQL release.
Returns the population standard deviation of expr
. This is
an extension to standard SQL. The standard SQL function STDDEV_POP()
can be used instead.
This function returns NULL
if there were no matching rows.
Returns the population standard deviation of expr
. This
function is provided for compatibility with Oracle. The standard SQL function STDDEV_POP()
can be used instead.
This function returns NULL
if there were no matching rows.
Returns the population standard deviation of expr
(the
square root of VAR_POP()
).
You can also use STD()
or STDDEV()
, which are equivalent but not standard SQL.
STDDEV_POP()
returns NULL
if there were
no matching rows.
Returns the sample standard deviation of expr
(the square
root of VAR_SAMP()
.
STDDEV_SAMP()
returns NULL
if there were no matching rows.
Returns the sum of expr
. If the return set has no rows, SUM()
returns
NULL
. The DISTINCT
keyword can be used to
sum only the distinct values of expr
.
SUM()
returns
NULL
if there were no matching rows.
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 use VARIANCE()
, which is equivalent but is not standard SQL.
VAR_POP()
returns NULL
if there were no matching rows.
Returns the sample variance of expr
. That is, the
denominator is the number of rows minus one.
VAR_SAMP()
returns NULL
if there were no matching rows.
Returns the population standard variance of expr
. This is
an extension to standard SQL. The standard SQL function VAR_POP()
can be used instead.
VARIANCE()
returns NULL
if there were no matching rows.