6.3.7.1 GROUP BY Functions

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

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 optimised 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 optimisation 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 SQL-99 you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...).

AVG(expr)

Returns the average value of expr:

mysql> SELECT student_name, AVG(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

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 5.4.3 How MySQL Uses Indexes.

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

In 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 will be rectified.

SUM(expr)

Returns the sum of expr. Note that if the return set has no rows, it returns NULL!

GROUP_CONCAT(expr)

Full syntax:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
             [SEPARATOR str_val])

This function was added in MySQL version 4.1. It returns a string result with the concatenated values from a group:

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 the string value which 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 variable group_concat_max_len in your configuration. The syntax to do this at runtime is:

SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;

If a maximum length has been set, the result is truncated to this maximum length. The GROUP_CONCAT() function is an enhanced implementation of the basic LIST() function supported by Sybase SQL Anywhere. GROUP_CONCAT() is backward compatible with the extremely limited functionality of LIST(), if only one column and no other options are specified. LIST() does have a default sorting order.

VARIANCE(expr)

Returns the standard variance of expr. This is an extension to SQL-99 (available only in version 4.1 or later).

STD(expr)

STDDEV(expr)

Returns the standard deviation of expr. This is an extension to SQL-99. The STDDEV() form of this function is provided for Oracle compatibility.

BIT_OR(expr)

Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. Function returns 0 if there was no matching rows.

BIT_AND(expr)

Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. Function returns -1 if there was no matching rows.

MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items. For example, you don't need to group on customer.name in the following query:

mysql> SELECT order.custid,customer.name,MAX(payments)
    ->        FROM order,customer
    ->        WHERE order.custid = customer.custid
    ->        GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you don't run in ANSI mode.

Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.

In some cases, you can use MIN() and MAX() to obtain a specific column value even if it isn't unique. The following gives the value of column from the row containing the smallest value in the sort column:

SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)

See section 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field.

Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to follow SQL-99, you can't use expressions in GROUP BY or ORDER BY clauses. You can work around this limitation by using an alias for the expression:

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
    ->        GROUP BY id,val ORDER BY val;

In MySQL Version 3.23 you can do:

mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();

User Comments

A wonderful alternative to group_concat for versions prior 4.1 is MyGroupConcat: A MySQL UDF aggregate function for string concatenation By Emmanuel Kartmann
http://www.codeproject.com/useritems/MyGroupConcat.asp

Add your own comment.