MySQL Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ... Using and misusing GROUP BY
Example
SELECT item.item_id, item.name, /* not SQL-92 */
COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id, uses.item_id
GROUP BY item.item_id
will show the rows in a table called item, and show the count of related rows in a table called uses. This works well, but unfortunately it's not standard SQL-92.
Why not? because the SELECT clause (and the ORDER BY clause) in GROUP BY queries must contain columns that are
- mentioned in the
GROUP BYclause, or - aggregate functions such as
COUNT(),MIN(), and the like.
This example's SELECT clause mentions item.name, a column that does not meet either of those criteria. MySQL 5.6 and earlier will reject this query if the SQL mode contains ONLY_FULL_GROUP_BY.
This example query can be made to comply with the SQL-92 standard by changing the GROUP BY clause, like this.
SELECT item.item_id, item.name,
COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id, uses.item_id
GROUP BY item.item_id, item.name
The later SQL-99 standard allows a SELECT statement to omit unaggregated columns from the group key if the DBMS can prove a functional dependence between them and the group key columns. Because item.name is functionally dependent on item.item_id, the initial example is valid SQL-99. MySQL gained a functional dependence prover in version 5.7. The original example works under ONLY_FULL_GROUP_BY.
