I am trying to get the top 5 products using a Mysql Query but it's giving me an error

Question

SELECT producttb.ProductName, producttb.ProductID, sum(ordertb.Qty) as Q from ordertb INNER JOIN producttb  on ordertb.ProductID = producttb.ProductID where ordertb.OrderDate between '2022-11-30' and '2023-01-18' group by producttb.ProductName order by Q desc LIMIT 5;

I am trying to get the top 5 products with the query above, but it's giving me an error.

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ims.producttb.ProductID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

use ANY_VALUE() in the select for the fieldname not used in the group by statement

SELECT producttb.ProductName, ANY_VALUE(producttb.ProductID), sum(ordertb.Qty) as Q
from ordertb
    INNER JOIN producttb
    on ordertb.ProductID = producttb.ProductID
where ordertb.OrderDate between '2022-11-30' and '2023-01-18'
group by producttb.ProductName
order by Q desc LIMIT 5;