How do I make my query mysql 5.7 compliant without changing my.ini file

I have just upgraded to mysql 5.7.14 and it has caused a problem with one of my queries, and I get the error.

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'salesinvoice.InvoiceDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Now the solution apparently was to edit the my.ini file and add this line

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Now this works, and now my query runs fine, but I would rather make my query mysql 5.7 compliant rather than having to add lines to my ini file, which might or might not work on our server. I have no access to the ini file on the server, but I have in my local server running wampserver 3.06.

Here is my query

SELECT DATE_FORMAT(`InvoiceDate`,'%Y-%m') as InvoiceDate,
       ROUND((SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`)+`VATValue`,2) as amount 
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products`
  on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
WHERE `InvoiceDate` < Now()
  and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
GROUP BY Month(`InvoiceDate`)

How do I change my query to be mysql 5.7 compliant, and what does the error message really mean?

Solution

From MySQL Documentation

MySQL 5.7.5 and up implements detection of functional dependence. 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. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

To tell MySQL to accept the query, you can use the ANY_VALUE() function on the field which produced the error.

SELECT ANY_VALUE(DATE_FORMAT(`InvoiceDate`,'%Y-%m')) as `formatedInvoiceDate`,
       ROUND((SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`)+`VATValue`,2) as `amount` 
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products`
  on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
WHERE `InvoiceDate` < Now()
  and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
GROUP BY Month(`InvoiceDate`).

Alternatively, disable ONLY_FULL_GROUP_BY.

A little more about ANY_VALUE() function can be found in MySQL Documentation on Miscellaneous Functions (make sure to read the example).

Synoptically, ANY_VALUE() function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.