MySQL Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ... Misusing GROUP BY with SELECT *, and how to fix it.
Example
Sometimes a query looks like this, with a * in the SELECT clause.
SELECT item.*, /* nonstandard */
COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id, uses.item_id
GROUP BY item.item_id
Such a query needs to be refactored to comply with the ONLY_FULL_GROUP_BY standard.
To do this, we need a subquery that uses GROUP BY correctly to return the number_of_uses value for each item_id. This subquery is short and sweet, because it only needs to look at the uses table.
SELECT item_id, COUNT(*) number_of_uses
FROM uses
GROUP BY item_id
Then, we can join that subquery with the item table.
SELECT item.*, usecount.number_of_uses
FROM item
JOIN (
SELECT item_id, COUNT(*) number_of_uses
FROM uses
GROUP BY item_id
) usecount ON item.item_id = usecount.item_id
This allows the GROUP BY clause to be simple and correct, and also allows us to use the * specifier.
Note: nevertheless, wise developers avoid using the * specifier in any case. It's usually better to list the columns you want in a query.
