12.19.3 MySQL Handling of GROUP BY

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the nonaggregated name column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

A similar MySQL extension applies to the HAVING clause. In standard SQL, a query cannot refer to nonaggregated columns in the HAVING clause that are not named in the GROUP BY clause. To simplify calculations, a MySQL extension permits references to such columns. This extension assumes that the nongrouped columns have the same group-wise values. Otherwise, the result is nondeterministic.

To disable the MySQL GROUP BY extension and enable standard SQL behavior, enable the ONLY_FULL_GROUP_BY SQL mode. In this case, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.

The select list extension also applies to ORDER BY. That is, you can refer to nonaggregated columns in the ORDER BY clause that do not appear in the GROUP BY clause. (However, as mentioned previously, ORDER BY does not affect which values are chosen from nonaggregated columns; it only sorts them after they have been chosen.) This extension does not apply if the ONLY_FULL_GROUP_BY SQL mode is enabled.

If a query has aggregate functions and no GROUP BY clause, it cannot have nonaggregated columns in the select list, HAVING condition, or ORDER BY list with ONLY_FULL_GROUP_BY enabled:

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

Without GROUP BY, there is a single group and it is nondeterministic which name value to choose for the group.

Another MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Enabling ONLY_FULL_GROUP_BY prevents this. For example, the following query returns name values that occur only once in table orders; the query is accepted regardless of whether ONLY_FULL_GROUP_BY is enabled:

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

The following query is accepted only if ONLY_FULL_GROUP_BY is disabled.

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

If you are trying to follow standard SQL, you can use only column 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 noncolumn expressions in GROUP BY clauses, so the alias is unnecessary:

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