Copyright (C) 2000-2012 |
GNU Info (mysql.info)Group by functionsFunctions for Use with `GROUP BY' Clauses ========================================= 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 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; `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 ANSI SQL you would have to do a concatenation of all expressions inside `CODE(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. Note: MySQL indexes. mysql> select student_name, MIN(test_score), MAX(test_score) from student GROUP BY student_name; `SUM(expr)' Returns the sum of `expr'. Note that if the return set has no rows, it returns NULL! `STD(expr)' `STDDEV(expr)' Returns the standard deviation of `expr'. This is an extension to ANSI SQL. 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. `BIT_AND(expr)' Returns the bitwise `AND' of all bits in `expr'. The calculation is performed with 64-bit (`BIGINT') precision. 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 ANSI 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) Note: example-Maximum-column-group-row. Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to follow ANSI SQL, 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(); automatically generated by info2www version 1.2.2.9 |