MySQL group by issue: incompatible with sql_mode=only_full_group_by

Question

Task: Show per shop the most bought product.
3 tables: shop, product, payment. If there is a tie on products that are sold to 1 shop, it doesn't matter which product gets picked, it just have to pick one of them.

I have an issue 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 would work. This will be the result which is correct:

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

But on 5.7 I'm getting the incompatible with sql_mode=only_full_group_by, because product_id in the 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 the product_id in the group by like so:

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)

That fixes the error but that returns the wrong result, it does not make the shop column unique anymore. I'm getting 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 everybody's life easier: http://sqlfiddle.com/#!9/ca12bf9/6

Answer 1

Your requirement is surprisingly complex. That you got a useful result from MySQL's nonstandard extension to GROUP BY is entirely accidental. Results based on that extension resemble nothing so much as a talking donkey. It's not amazing it works badly, it's amazing it works at all.

Here's what you have to do.

(1) summarize sales by product and shop. (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 shop, by summarizing (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) Join (1) to (2) to retrieve the identity of the product or products that sold the most in each shop. (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 provided has a tie. So two different products show up as best sellers in one of your shops.

It's this kind of query that puts the structured in structured query language..