**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 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())) FROM`time_col`

; SELECT FROM_DAYS(SUM(TO_DAYS(`tbl_name`

))) FROM`date_col`

;`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.

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. The calculation is performed with 64-bit (`expr`

`BIGINT`

) precision.`BIT_AND()`

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. The calculation is performed with 64-bit (`expr`

`BIGINT`

) precision.`BIT_OR()`

returns`0`

if there were no matching rows.Returns the bitwise

`XOR`

of all bits in. The calculation is performed with 64-bit (`expr`

`BIGINT`

) precision.`BIT_XOR()`

returns`0`

if there were no matching rows.Returns a count of the number of non-

`NULL`

values ofin the rows retrieved by a`expr`

`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 non-

`NULL`

values.`expr`

`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 non-

`NULL`

values from a group. It returns`NULL`

if there are no non-`NULL`

values. The full syntax is as follows:GROUP_CONCAT([DISTINCT]

[,`expr`

...] [ORDER BY {`expr`

|`unsigned_integer`

|`col_name`

} [ASC | DESC] [,`expr`

...]] [SEPARATOR`col_name`

])`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 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, whereis an unsigned integer:`val`

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, however, this produces the same result as omitting`expr`

`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, however, this produces the same result as omitting`expr`

`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

. This is an extension to standard SQL. The standard SQL function`expr`

`STDDEV_POP()`

can be used instead.`STD()`

returns`NULL`

if there were no matching rows.Returns the population standard deviation of

. This function is provided for compatibility with Oracle. The standard SQL function`expr`

`STDDEV_POP()`

can be used instead.`STDDEV()`

returns`NULL`

if there were no matching rows.Returns the population standard deviation of

(the square root of`expr`

`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

(the square root of`expr`

`VAR_SAMP()`

.`STDDEV_SAMP()`

returns`NULL`

if there were no matching rows.Returns the sum of

. If the return set has no rows,`expr`

`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

. 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`expr`

`VARIANCE()`

, which is equivalent but is not standard SQL.`VAR_POP()`

returns`NULL`

if there were no matching rows.Returns the sample variance of

. That is, the denominator is the number of rows minus one.`expr`

`VAR_SAMP()`

returns`NULL`

if there were no matching rows.Returns the population standard variance of

. This is an extension to standard SQL. The standard SQL function`expr`

`VAR_POP()`

can be used instead.`VARIANCE()`

returns`NULL`

if there were no matching rows.