GNU Info

(mysql.info)Group by functions

---

Prev: Perl support Up: Placeholder

---

Enter node , (file) or (file)node

Functions 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();