Error in SQL query GROUP BY clause; sql_mode=only_full_group_by

Question

Please help me with this SQL query; when I use a GROUP BY clause, I get this error from Mysql:

This is incompatible with sql_mode=only_full_group_by

I want different values on column: B.shopname.

SELECT 
    A.id,
    COUNT(A.id),
    A.idShop,
    B.shopname
FROM 
    A 
INNER JOIN 
    B ON A.idShop = B.id
GROUP BY 
    B.shopname

Answer 1

The GROUP BY columns are incompatible with the SELECT columns. Why? The query is trying to SELECT three unaggregated columns, A.id, A.idShop, and B.shopname. However, the GROUP BY only includes one of those.

The general recommendation is to add all unaggregated columns into the GROUP BY:

SELECT A.id, COUNT(A.id), A.idShop, B.shopname
FROM A INNER JOIN
     B
     ON A.idShop = B.id
GROUP BY A.id, A.idShop, B.shopname;

But I don't think this will do what you want. I am guessing that the COUNT() will always be 1. I suspect you want:

SELECT B.idShop, B.shopname, COUNT(*)
FROM A INNER JOIN
     B
     ON A.idShop = B.id
GROUP BY B.id, B.shopname;

Notes:

• A.id is no longer in the SELECT. It is not appropriate because you want to count the values.
• I doubt that A.id is ever NULL, so counting the non-NULL values of the column just clutters the query. COUNT(*) is clearer (although some folks prefer COUNT(1)).
• It is better to have GROUP BY keys all be from the same table because it gives the optimizer more options.