MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the select list that do not appear in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. For example, you need not group on customer.name in the following query:

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.

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

A similar MySQL extension applies to the HAVING clause. The SQL standard does not permit the HAVING clause to name any column not found in the GROUP BY clause if it is not enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.

If the ONLY_FULL_GROUP_BY SQL mode is enabled, the MySQL extension to GROUP BY does not apply. That is, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause if not enclosed in an aggregate function.

For example, the following query returns id values that occur only once in table t1:

SELECT id, COUNT(id) FROM t1
  GROUP BY id
  HAVING COUNT(id) = 1;

However, the result of the following similar query that uses an alias for the aggregated column depends on the SQL mode:

SELECT id, COUNT(id) AS c FROM t1
  GROUP BY id
  HAVING c = 1;

In this case, a non-grouping field 'c' is used in HAVING clause error occurs if ONLY_FULL_GROUP_BY is enabled because the extension does not apply. The column c in the HAVING clause is not enclosed in an aggregate function (instead, it is an aggregate function).

The select list extension also applies to ORDER BY. That is, you can use nonaggregated columns or calculations in the ORDER BY clause that do not appear in the GROUP BY clause. This extension does not apply if the ONLY_FULL_GROUP_BY SQL mode is enabled.

In some cases, you can use MIN() and MAX() to obtain a specific column value even if it is not 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.6.4, “The Rows Holding the Group-wise Maximum of a Certain Column”.

If you are trying to follow standard SQL, you cannot use expressions in GROUP BY clauses. As a workaround, use an alias for the expression:

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

MySQL permits expressions in GROUP BY clauses, so the alias is unnecessary:

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);