 MySQL 5.1 Reference Manual :: 11 Functions and Operators :: 11.12 Functions and Modifiers for Use with GROUP BY Clauses :: 11.12.1 GROUP BY (Aggregate) Functions

 MySQL 5.1 Reference Manual
 Preface, Notes, Licenses
 1 General Information
 2 Installing and Upgrading MySQL
 3 Tutorial
 4 MySQL Programs
 5 MySQL Server Administration
 6 Backup and Recovery
 7 Optimization
 8 Language Structure
 9 Internationalization and Localization
 10 Data Types
 11 Functions and Operators
 12 SQL Statement Syntax
 13 Storage Engines
 14 High Availability and Scalability
 15 MySQL Enterprise Monitor
 16 Replication
 17 MySQL Cluster NDB 6.X/7.X
 18 Partitioning
 19 Stored Programs and Views
 20 INFORMATION_SCHEMA Tables
 21 Connectors and APIs
 22 Extending MySQL
 A MySQL 5.1 Frequently Asked Questions
 B Errors, Error Codes, and Common Problems
 C MySQL Change History
 D Restrictions and Limits
 Index
 Standard Index
 C Function Index
 Command Index
 Function Index
 INFORMATION_SCHEMA Index
 Transaction Isolation Level Index
 JOIN Types Index
 Operator Index
 Option Index
 Privileges Index
 SQL Modes Index
 Status Variable Index
 Statement/Syntax Index
 System Variable Index
Table 11.20. Aggregate (
GROUP BY
) FunctionsName Description AVG()
Return the average value of the argument BIT_AND()
Return bitwise and BIT_OR()
Return bitwise or BIT_XOR()
(v4.1.1)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()
(v4.1)Return a concatenated string MAX()
Return the maximum value MIN()
Return the minimum value STD()
Return the population standard deviation STDDEV_POP()
(v5.0.3)Return the population standard deviation STDDEV_SAMP()
(v5.0.3)Return the sample standard deviation STDDEV()
Return the population standard deviation SUM()
Return the sum VAR_POP()
(v5.0.3)Return the population standard variance VAR_SAMP()
(v5.0.3)Return the sample variance VARIANCE()
(v4.1)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 11.12.3, “GROUP BY
andHAVING
with Hidden Columns”.For numeric arguments, the variance and standard deviation functions return a
DOUBLE
value. TheSUM()
andAVG()
functions return aDECIMAL
value for exactvalue arguments (integer orDECIMAL
), and aDOUBLE
value for approximatevalue arguments (FLOAT
orDOUBLE
).The
SUM()
andAVG()
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, you can 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()
orAVG()
that expect a numeric argument cast the argument to a number if necessary. ForSET
orENUM
values, the cast operation causes the underlying numeric value to be used.
Returns the average value of
. Theexpr
DISTINCT
option can be used to return the average of the distinct values ofexpr
.AVG()
returnsNULL
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 inexpr
. The calculation is performed with 64bit (BIGINT
) precision.This function returns
18446744073709551615
if there were no matching rows. (This is the value of an unsignedBIGINT
value with all bits set to 1.) 
Returns the bitwise
OR
of all bits inexpr
. 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 inexpr
. 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 non
NULL
values ofexpr
in the rows retrieved by aSELECT
statement. The result is aBIGINT
value.COUNT()
returns0
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 containNULL
values.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 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 asInnoDB
, 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 non
NULL
expr
values.COUNT(DISTINCT)
returns0
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 insideCOUNT(DISTINCT ...)
. 
This function returns a string result with the concatenated non
NULL
values from a group. It returnsNULL
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 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 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
.See also
CONCAT()
andCONCAT_WS()
: Section 11.4, “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 7.4.4, “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
.MAX()
returnsNULL
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 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. 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 7.4.4, “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
.MIN()
returnsNULL
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 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. 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 functionSTDDEV_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 functionSTDDEV_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 ofVAR_POP()
). You can also useSTD()
orSTDDEV()
, which are equivalent but not standard SQL.STDDEV_POP()
returnsNULL
if there were no matching rows. 
Returns the sample standard deviation of
expr
(the square root ofVAR_SAMP()
.STDDEV_SAMP()
returnsNULL
if there were no matching rows. 
Returns the sum of
expr
. If the return set has no rows,SUM()
returnsNULL
. TheDISTINCT
keyword can be used in MySQL 5.1 to sum only the distinct values ofexpr
.SUM()
returnsNULL
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 useVARIANCE()
, which is equivalent but is not standard SQL.VAR_POP()
returnsNULL
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()
returnsNULL
if there were no matching rows. 
Returns the population standard variance of
expr
. This is an extension to standard SQL. The standard SQL functionVAR_POP()
can be used instead.VARIANCE()
returnsNULL
if there were no matching rows.