CODEWITHSUNDEEP
mysql
user2399646
user2399646

Reputation:

extreme sql_mode=only_full_group_by

SELECT DISTINCT
    `A/C#` AS `A/C#`,
    `MyRef` AS `MyRef`,
    DATEDIFF('2017-06-30', `Date`) AS `Days`,
    `Dr` - `Cr` AS `Balance`
FROM
    `BERNIE`.`answer`
GROUP BY
    `A/C#`,
    `MyRef`
ORDER BY
    BINARY `A/C#`,
    BINARY `MyRef`,
    `Days`,
    `Balance`

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

Can make the error go away by including Date, Dr, and Cr in the GROUP BY.

I get the GROUP BY thing (well maybe..), but this seems extreme?

Update: Adding the aliases of the calculated fields (Days & Balance) to the GROUP BY also runs gets rid of the error. Now I'm more confused.

Upvotes: 0

Views: 55

Answers (2)

user2399646
user2399646

Reputation:

Remove the GROUP BY altogether. It is not appropriate in this case as there is no aggregation.

Upvotes: 1

Ramki
Ramki

Reputation: 472

You can try to disable the only_full_group_by setting by executing the following:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

OR 

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

Upvotes: 0

Related Questions