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.
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)