phpmysqlmysql-error-1055

SELECT list is not in GROUP BY clause ERROR MySQL


I'm just a beginner in using MySQL commands. I've searched this error but their problems are not the same as mine. I'm having a hard time understanding the error where it says SELECT list is not in GROUP BY clause and contains nonaggregated column 'japorms.p.item_price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I've searched also the meaning of functionally dependent but still failed to understand.

here is my query:

SELECT
  sum(od.item_qty) as item_qty,
  p.item_name as item_name,
  p.item_price as item_price, 
  od.size as size
from order_details as od, productmaster as p
where p.id = od.item_id 
group by p.item_name

If I remove the p.item_price and od.size from my query, it accepts the p.item_name . I wonder why because p.item_name and p.item_price are in the same table.


Solution

  • you need to mention all columns, not being an aggregation function, in group by:

    SELECT
      SUM(od.item_qty) AS item_qty,
      p.item_name AS item_name,
      p.item_price AS item_price, 
      od.size AS size
    FROM order_details AS od, 
    JOIN productmaster AS p ON p.id = od.item_id 
    GROUP BY p.item_name, p.item_price, od.size
    

    in this case, SUM() is such a function, as would be MAX, MIN, COUNT etc

    (and changed to an explict written join)