(mysql.info) group-by-functions
Info Catalog
(mysql.info) group-by-functions-and-modifiers
(mysql.info) group-by-functions-and-modifiers
(mysql.info) group-by-modifiers
12.10.1 `GROUP BY' (Aggregate) Functions
----------------------------------------
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.
The `SUM()' and `AVG()' aggregate functions do not work with temporal
values. (They convert the values to numbers, which loses the part after
the first non-numeric 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))) FROM TBL_NAME;
SELECT FROM_DAYS(SUM(TO_DAYS(DATE_COL))) FROM TBL_NAME;
* `AVG([DISTINCT] EXPR)'
Returns the average value of `EXPR'. The `DISTINCT' option can be
used as of MySQL 5.0.3 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;
* `BIT_AND(EXPR)'
Returns the bitwise `AND' of all bits in EXPR. The calculation is
performed with 64-bit (`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.)
* `BIT_OR(EXPR)'
Returns the bitwise `OR' of all bits in EXPR. The calculation is
performed with 64-bit (`BIGINT') precision.
This function returns `0' if there were no matching rows.
* `BIT_XOR(EXPR)'
Returns the bitwise `XOR' of all bits in EXPR. The calculation is
performed with 64-bit (`BIGINT') precision.
This function returns `0' if there were no matching rows.
* `COUNT(EXPR)'
Returns a count of the number of non-`NULL' values in the rows
retrieved by a `SELECT' statement.
`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' and `BDB', 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 different non-`NULL' 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 ...)'.
* `GROUP_CONCAT(EXPR)'
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 [,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. You can eliminate duplicate values by using
`DISTINCT'. If you want to sort values in the result, you should
use `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. `SEPARATOR' is
followed by the string value that should be inserted between
values of result. The default is a comma (‘`,'’). You can
eliminate the separator altogether by specifying `SEPARATOR '''.
You can set a maximum allowed length with the
`group_concat_max_len' system variable. (The default value is
1024.) The syntax to do this at runtime is as follows, where VAL
is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = VAL;
If a maximum length has been set, the result is truncated to this
maximum length.
Beginning with MySQL 5.0.19, the type returned by `GROUP_CONCAT()'
is always `VARCHAR' unless `group_concat_max_len' is greater than
512, in which case, it returns a `BLOB'. (Previously, it returned
a `BLOB' with `group_concat_max_len' greater than 512 only if the
query included an `ORDER BY' clause.)
See also `CONCAT()' and `CONCAT_WS()': string-functions.
* `MIN([DISTINCT] EXPR)', `MAX([DISTINCT] EXPR)'
Returns the minimum or maximum value of EXPR. `MIN()' and `MAX()'
may take a string argument; in such cases they return the minimum
or maximum string value. See mysql-indexes. The
`DISTINCT' keyword can be used to find the minimum or maximum of
the distinct values of EXPR, however, this produces the same
result as omitting `DISTINCT'.
`MIN()' and `MAX()' return `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()', `MAX()', and other aggregate functions, 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.
* `STD(EXPR)' `STDDEV(EXPR)'
Returns the population standard deviation of EXPR. This is an
extension to standard SQL. The `STDDEV()' form of this function is
provided for compatibility with Oracle. As of MySQL 5.0.3, the
standard SQL function `STDDEV_POP()' can be used instead.
These functions return `NULL' if there were no matching rows.
* `STDDEV_POP(EXPR)'
Returns the population standard deviation of EXPR (the square root
of `VAR_POP()'). This function was added in MySQL 5.0.3. Before
5.0.3, you can use `STD()' or `STDDEV()', which are equivalent but
not standard SQL.
`STDDEV_POP()' returns `NULL' if there were no matching rows.
* `STDDEV_SAMP(EXPR)'
Returns the sample standard deviation of EXPR (the square root of
`VAR_SAMP()'. This function was added in MySQL 5.0.3.
`STDDEV_SAMP()' returns `NULL' if there were no matching rows.
* `SUM([DISTINCT] EXPR)'
Returns the sum of EXPR. If the return set has no rows, `SUM()'
returns `NULL'. The `DISTINCT' keyword can be used in MySQL 5.0 to
sum only the distinct values of EXPR.
`SUM()' returns `NULL' if there were no matching rows.
* `VAR_POP(EXPR)'
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. This function was added in MySQL
5.0.3. Before 5.0.3, you can use `VARIANCE()', which is equivalent
but is not standard SQL.
`VAR_POP()' returns `NULL' if there were no matching rows.
* `VAR_SAMP(EXPR)'
Returns the sample variance of EXPR. That is, the denominator is
the number of rows minus one. This function was added in MySQL
5.0.3.
`VAR_SAMP()' returns `NULL' if there were no matching rows.
* `VARIANCE(EXPR)'
Returns the population standard variance of EXPR. This is an
extension to standard SQL. As of MySQL 5.0.3, the standard SQL
function `VAR_POP()' can be used instead.
`VARIANCE()' returns `NULL' if there were no matching rows.
Info Catalog
(mysql.info) group-by-functions-and-modifiers
(mysql.info) group-by-functions-and-modifiers
(mysql.info) group-by-modifiers
automatically generated byinfo2html