Tutorial by Examples | RIP Tutorial

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 unfo...

MySQL • Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...

Misusing GROUP BY to return unpredictable results: Murphy's Law

SELECT item.item_id, uses.category, /* nonstandard */ 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. It will also show the va...

MySQL • Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...

Misusing GROUP BY with SELECT *, and how to fix it.

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 sta...

MySQL • Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...

ANY_VALUE()

SELECT item.item_id, ANY_VALUE(uses.tag) tag, COUNT(*) number_of_uses FROM item JOIN uses ON item.item_id, uses.item_id GROUP BY item.item_id shows the rows in a table called item, the count of related rows, and one of the values in the related table called uses. You can th...

MySQL • Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...