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