How to solve SQL Error (1055): Expression #7 of Select list is not in group by-CodePudding

SQL Error (1055): Expression #7 of Select list is not in group by Clause and contains nonaggregated column 'test_db.pid.product_id' which is not Functionally dependent on columns in group by clause; this is incomplete with sql_mode=only_full_group_by

SELECT pro.name AS product_name,pro.id AS product_id,pro.model AS product_model,bnd.name AS brand_name,sub_cat.name AS sub_category_name,cat.name AS category_name,
IFNULL(sum(pid.quantity)-(
                  SELECT sum(pbd.quantity)
                           from product_bill_details AS pbd
                           WHERE pbd.product_id=pid.product_id
                           GROUP BY pbd.product_id),SUM(pid.quantity))  AS quantity
FROM product_issue_details AS pid
LEFT JOIN product_issue_masters AS pim on pid.product_issue_master_id=pim.id
LEFT JOIN products AS pro ON pro.id=pid.product_id
LEFT JOIN brands AS bnd ON pro.brand_id=bnd.id
LEFT JOIN sub_categories AS sub_cat on sub_cat.id=bnd.sub_category_id
LEFT JOIN categories AS cat ON cat.id=sub_cat.category_id
WHERE pim.project_id=1
GROUP BY pro.id,pro.name,pro.model,bnd.name,cat.name,sub_cat.name

CodePudding user response：

if you're using PHPMyadmin click on Server:servername in top header, then click Variables, after that search for sql_mode and then click edit and remove the very 1st value ONLY_FULL_GROUP_BY and save it. This solution works as long as you don't log out.

And for MySQL workbench click on Server and then click Status and System variables, after that click on System variables and search for sql_mode and remove ONLY_FULL_GROUP_BY

CodePudding user response：

Try this one: You won't get the same mistake If the rest of your query is okay

SELECT pro.name AS product_name,pro.id AS product_id,pro.model AS product_model,bnd.name AS brand_name,sub_cat.name AS sub_category_name,cat.name AS category_name,
IFNULL(sum(pid.quantity)-(
                  SELECT sum(pbd.quantity)
                           from product_bill_details AS pbd
                           WHERE pbd.product_id=pid.product_id
                           GROUP BY pbd.product_id),SUM(pid.quantity))  AS quantity
FROM product_issue_details AS pid
LEFT JOIN product_issue_masters AS pim on pid.product_issue_master_id=pim.id
LEFT JOIN products AS pro ON pro.id=pid.product_id
LEFT JOIN brands AS bnd ON pro.brand_id=bnd.id
LEFT JOIN sub_categories AS sub_cat on sub_cat.id=bnd.sub_category_id
LEFT JOIN categories AS cat ON cat.id=sub_cat.category_id
WHERE pim.project_id=1
GROUP BY product_id,product_name,product_model,brand_name,category_name,sub_category_name