MySQL release group: incompatible with sql_mode = only_full_group_by

Question

Objective: show the best-selling product in the store.
3 tables: shop, product, payment. If there is a link to products that are sold in 1 store, it does not matter which product is selected, you just need to choose one of them.

I have a problem with a group by clause on this query:

SELECT shop_id, product_id, ( SELECT COUNT(*) FROM payment WHERE product.product_id = payment.product_id ) sold FROM product GROUP BY shop_id HAVING MAX(sold) 

In MySQL 5.6 or lower, this query will work. This will be the correct result:

 shop_id | product_id | sold 1 1 3 2 3 1 3 5 1 

But on 5.7, I get incompatible with sql_mode = only_full_group_by, because the product_id in select contains non-aggregated data.

Full error message:

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

So, the solution I thought would be adding product_id to the group like this:

 SELECT shop_id, product_id, ( SELECT COUNT(*) FROM payment WHERE product.product_id = payment.product_id ) sold FROM product GROUP BY shop_id, product_id HAVING MAX(sold) 

This corrects the error, but returns an incorrect result; this does not make the store’s unique column unique. I get this:

 shop_id | product_id | sold 1 1 3 1 2 4 2 3 1 2 4 1 3 5 1 

SQLfiddle uses MySQL 5.6, but to make life easier for everyone: http://sqlfiddle.com/#!9/ca12bf9/6

Answer 1

Your requirement is surprisingly complex. The fact that you got a useful result from a non-standard MySQL extension to GROUP BY is completely random. Results based on this extension are nothing like a talking donkey. It is not surprising that this works poorly; it is surprising that it works in general.

Here is what you need to do.

(1) summarize sales by products and stores. ( http://sqlfiddle.com/#!9/ca12bf9/12/0 )

  select product.product_id, product.shop_id, count(*) sale_count from product join payment on product.product_id = payment.product_id group by product.product_id, product.shop_id 

(2) find the number of units sold for the best-selling product in each store by summing up (1) ( http://sqlfiddle.com/#!9/ca12bf9/13/0 )

  SELECT MAX(sale_count) max_sale_count, shop_id FROM ( select product.product_id, product.shop_id, count(*) sale_count from product join payment on product.product_id = payment.product_id group by product.product_id, product.shop_id ) findmax GROUP BY shop_id 

(3) Attach (1) to (2) to get the identity of the product or products that were sold most in each store. ( http://sqlfiddle.com/#!9/ca12bf9/11/0 )

 SELECT a.product_id, a.shop_id, b.max_sale_count FROM ( select product.product_id, product.shop_id, count(*) sale_count from product join payment on product.product_id = payment.product_id group by product.product_id, product.shop_id ) a JOIN ( SELECT MAX(sale_count) max_sale_count, shop_id FROM ( select product.product_id, product.shop_id, count(*) sale_count from product join payment on product.product_id = payment.product_id group by product.product_id, product.shop_id ) findmax GROUP BY shop_id ) b ON a.shop_id = b.shop_id AND a.sale_count = b.max_sale_count 

The data you provide has a tie. Thus, two different products appear as the best sellers in one of your stores.

This is a query that places a structured query into a structured query language.

Answer 2

Or you can set sql_mode to empty using the set sql_mode ='' in my.ini

or you can use ANY_VALUE(product_id)

when using a non-aggregate column with a group it never guarantees what will be the result.

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

update 1 : http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html