(mysql.info.gz) GROUP-BY-Functions
Info Catalog
(mysql.info.gz) Group by functions and modifiers
(mysql.info.gz) Group by functions and modifiers
(mysql.info.gz) GROUP-BY-Modifiers
12.9.1 `GROUP BY' (Aggregate) Functions
---------------------------------------
If you use a group function in a statement containing no `GROUP BY'
clause, it is equivalent to grouping on all rows.
`AVG(EXPR)'
Returns the average value of `EXPR'.
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.
As of MySQL 4.0.17, this function returns `18446744073709551615'
if there were no matching rows. (This is an unsigned `BIGINT'
value with all bits set to 1.) Before 4.0.17, the function
returns `-1' if there were no matching rows.
`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.
This function is available as of MySQL 4.1.1.
`COUNT(EXPR)'
Returns a count of the number of non-`NULL' values in the rows
retrieved by a `SELECT' statement.
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' and `ISAM' tables only,
because an exact record count is stored for these table types and
can be accessed very quickly. For transactional storage engines
(`InnoDB', `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.
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can get the number of distinct expression
combinations that don't contain `NULL' by giving a list of
expressions. In standard SQL, you would have to do a
concatenation of all expressions inside `COUNT(DISTINCT ...)'.
`COUNT(DISTINCT ...)' was added in MySQL 3.23.2.
`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 remove the
separator altogether by specifying `SEPARATOR '''.
You can set a maximum allowed length with the
`group_concat_max_len' system variable. 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.
`GROUP_CONCAT()' was added in MySQL 4.1.
Note: Before MySQL 4.1.6, there are some small limitations with
`GROUP_CONCAT()' for `BLOB' and `TEXT' values when it comes to
using `DISTINCT' together with `ORDER BY'. To work around this
limitation, use `MID(EXPR,1,255)' instead.
`MIN(EXPR)'
`MAX(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. MySQL indexes.
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 will be rectified.
`STD(EXPR)'
`STDDEV(EXPR)'
Returns the standard deviation of EXPR (the square root of
`VARIANCE()'). This is an extension to standard SQL. The `STDDEV()'
form of this function is provided for Oracle compatibility.
`SUM(EXPR)'
Returns the sum of EXPR. If the return set has no rows, `SUM()'
returns `NULL'.
`VARIANCE(EXPR)'
Returns the standard variance of EXPR (considering rows as the
whole population, not as a sample; so it has the number of rows as
denominator). This is an extension to standard SQL, available only
in MySQL 4.1 or later.
Info Catalog
(mysql.info.gz) Group by functions and modifiers
(mysql.info.gz) Group by functions and modifiers
(mysql.info.gz) GROUP-BY-Modifiers
automatically generated byinfo2html