In standard SQL, a query that includes a
BY clause cannot refer to nonaggregated columns in the
select list that are not named in the
BY clause. For example, this query is illegal in
standard SQL because the nonaggregated
column in the select list does not appear in the
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
must be omitted from the select list or named in the
GROUP BY clause.
MySQL extends the standard SQL use of
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
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 indeterminate.
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 indeterminate.
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
BY clause cannot be used in the select list or
HAVING clause unless enclosed in an aggregate
The select list extension also applies to
BY. That is, you can refer to nonaggregated columns in
ORDER BY clause that do not appear in the
GROUP BY clause. (However, as mentioned
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
mode is enabled.
If a query has aggregate functions and no
BY clause, it cannot have nonaggregated columns in the
HAVING condition, or
ORDER BY list with
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
GROUP BY, there is a single group and
it is indeterminate which
name value to
choose for the group.
Another MySQL extension to standard SQL permits references in
HAVING clause to aliased expressions in
the select list. Enabling
this. For example, the following query returns
name values that occur only once in table
orders; the query is accepted regardless of
SELECT name, COUNT(name) FROM orders GROUP BY name HAVING COUNT(name) = 1;
The following query is accepted only if
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
BY clauses, so the alias is unnecessary:
SELECT id, FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100);