MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

13.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 values from a group. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col ...]]
             [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.

Note: There are still some small limitations with GROUP_CONCAT() when it comes to using DISTINCT together with ORDER BY and using BLOB values. See Open Bugs and Design Deficiencies in MySQL.

GROUP_CONCAT() was added in MySQL 4.1.

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. See section How MySQL Uses 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. Note that if the return set has no rows, it 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.