GROUP BY statement throwing errors tutorial isn't-CodePudding

Hello im going through a tutorial on SQL joins and this statement below is throwing "sql_mode=only_full_group_by" error. I copied this directly from the tutorial and its working for him. Do I just need to disable this mode in the terminal?

    SELECT *
    FROM customers
    JOIN orders
            ON customers.id = orders.customer_id
    GROUP BY orders.customer_id;

CodePudding user response：

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. [1]

You can disable this, but it really makes sense to leave it enabled. Think about the following case:

id | type
---|-----
1  | foo
2  | foo
3  | bar

If you used a similar query to yours, select * from table group by type, what would you expect the output to be? Perhaps you expect two rows, foo and bar, but what do you expect to be in the id column, namely for the foo record? Should we just choose the earliest record? The latest record? A random record? This is what the error is telling you - instead of having the database engine choose for you, you must be explicit.

So, in your example, you need to think about what the resulting data should look like. Maybe instead of select * you want select customers.name, count(*) ..., etc.

The reason this works for the tutorial you're following is either they don't have only_full_group_by enabled or they're using a MySql version older than 5.7.5 where this was introduced. In this case, the server is free to choose any value from each group, which is probably not what you want.

Finally, if you want your console to behave in the same way your tutorial is behaving, you can disable ONLY_FULL_GROUP_BY in the console by:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

[1] https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html