Working with only_full_group_by

Question

I am trying to get the below statement to work with mysql v5.7.5, although really dont understand the wording at all in the error message and the documentation doesnt help me either.

The SQL Statement in question:

    SELECT 
        YEARWEEK(ts) AS YW, 
        DATE(ts) AS D,  
        SUM(`group-size`) AS `sum` 
    FROM `event` 
    GROUP BY YEARWEEK(ts);

The error I get:

Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'event.ts' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Can someone please explain in english how I can get it working? To my the SQL statement makes perfect sense however It is just throwing up the error every time.

*"To my the SQL statement makes perfect sense"* -- let's say there are only two rows in the table, both having the `ts` in the same week, in consecutive days (`2018-03-07` and `2018-03-08`). What value should your query produce for `D` and why do you think it is the correct one? — axiac, Mar 08 '18 at 14:28

score 1 · Answer 1 · answered Mar 08 '18 at 14:23

See longer answer:

In short, you need to add all columns that do not contain aggregate function in GROUP BY. In this particular case the "date(ds)" needs to be added:

SELECT 
    YEARWEEK(ts) AS YW, 
    DATE(ts) AS D,  
    SUM(`group-size`) AS `sum` 
FROM `event` 
GROUP BY YEARWEEK(ts), DATE(ts);

Working with only_full_group_by

1 Answers1