SELECT list is not in GROUP BY clause ERROR MySQL

Question

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.

Answer 1

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)

Answer 2

You can try something like this. When you use GROUP BY you should specify which column(s) are "grouped by" and which ones are aggregated with a function (eg. SUM, AVG,MIN, MAX, COUNT,...)

SELECT
  p.item_name as item_name,
  AVG(p.item_price) as item_price, 
  od.size as size,
  SUM(od.item_qty) as item_qty
from order_details as od
INNER JOIN productmaster as p ON p.id=od.item_id
group by p.item_name, od.size