MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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);