Error related to only_full_group_by in MySql

Question

I have created Query Which Gives error of only_full_group_by. I Want To change Query Not SET sql_mode=only_full_group_by

#1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hrdk.s.item_stock_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by#

This is the query that is giving me trouble:

SELECT `s`.`department_id`, `s`.`category_id`, `s`.`item_id`, `s`.`item_stock_id`, `s`.`tunch`, `cat`.`category_name`, `im`.`item_name`, `im`.`stock_method`, `cat`.`category_group_id`, SUM(s.grwt) AS grwt, SUM(s.ntwt) AS ntwt, sum(s.less) AS less, SUM(s.fine) AS fine
FROM `item_stock` `s`  
LEFT JOIN `item_master` `im` ON `im`.`item_id` = `s`.`item_id`  
LEFT JOIN `account` `pm` ON `pm`.`account_id` = `s`.`department_id`  
LEFT JOIN `category` `cat` ON `cat`.`category_id` = `s`.`category_id`  
WHERE (im.stock_method = 1 AND (`s`.`grwt` =0 OR `s`.`grwt` !=0)   
   OR (`im`.`stock_method` = 2 AND `s`.`grwt` != 0))  
   AND s.department_id IN(26,27,28,29,30,31,32,59)   
   AND `s`.`grwt` !=0  
   AND `s`.`department_id` = '26'   
GROUP BY `s`.`category_id`, `s`.`item_id`, if(`im`.`stock_method` = 1, `s`.`tunch`, "")  
ORDER BY `s`.`item_stock_id` DESC

Let me know if you need more information.

Fahmi Fahmi · Accepted Answer · 2019-05-03T05:56:24

You've to add all non aggregated columns in group by

SELECT s.department_id, s.category_id, s.item_id, s.item_stock_id, s.tunch, cat.category_name, im.item_name, im.stock_method, cat.category_group_id, SUM(s.grwt) AS grwt, SUM(s.ntwt) AS ntwt, sum(s.less) AS less, SUM(s.fine) AS fine 
FROM item_stock s LEFT JOIN item_master im ON im.item_id = s.item_id 
LEFT JOIN account pm ON pm.account_id = s.department_id 
LEFT JOIN category cat ON cat.category_id = s.category_id 
WHERE (im.stock_method = 1 AND (s.grwt =0 OR s.grwt !=0) OR (im.stock_method = 2 AND s.grwt != 0))AND s.department_id IN(26,27,28,29,30,31,32,59) AND s.grwt !=0 AND s.department_id = '26' 
GROUP BY s.department_id, s.category_id, s.item_id, s.item_stock_id, s.tunch, cat.category_name, im.item_name, im.stock_method, cat.category_group_id
ORDER BY s.item_stock_id DESC

Error related to only_full_group_by in MySql

1 Answers