title: MySQL group by issue: incompatible with sql_mode=only_full_group_by
body: <p>Task: Show per shop the most bought product.<br>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.</p>
  
  <p>I have an issue with a group by clause on this query:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>In MySQL 5.6 or lower this query would work.
  This will be the result which is correct:</p>
  
  <pre><code>shop_id | product_id | sold
  1         1            3
  2         3            1
  3         5            1
  </code></pre>
  
  <p>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.</p>
  
  <p>Full Error message:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So the solution I thought would be adding the product_id in the group by like so:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>That fixes the error but that returns the wrong result, it does not make the shop column unique anymore. I'm getting this:</p>
  
  <pre><code>shop_id | product_id | sold
  1         1            3
  1         2            4
  2         3            1
  2         4            1
  3         5            1
  </code></pre>
  
  <p>SQLfiddle uses MySQL 5.6 but to make everybody's life easier:  <a href="http://sqlfiddle.com/#!9/ca12bf9/6" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/ca12bf9/6</a></p>