Using and misusing GROUP BY

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

1. mentioned in the GROUP BY clause, or
2. 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.