Problem in GROUP BY in UPDATE with CASE

[newphpbees]

Hi.

I got an error:

Error Code : 1111
Invalid use of group function
(0 ms taken)

in my query:



UPDATE kanban_checker_doz kcd JOIN kanban_data kd ON (kcd.PCODE = kd.PCODE) SET kcd.count_doz_chemical_weighing = CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_chemicalweighing / 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P33'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.00 / 12))
when kd.PCODE = 'P35'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.00 / 12))
when kd.PCODE = 'P35M'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.60 / 12))
when kd.PCODE = 'P35W'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.50 / 12))
when kd.PCODE = 'P38'
then (SUM(kd.wip_chemicalweighing / 1000 / 26.70 / 12))
when kd.PCODE = 'P41'
then (SUM(kd.wip_chemicalweighing / 1000 / 25.00 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_chemicalweighing / 1000 / 18.88 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_chemicalweighing / 1000 / 0.00 / 12))
END,
kcd.count_doz_compounding = CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_compounding / 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_compounding / 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P33'
then (SUM(kd.wip_compounding / 1000 / 25.00 / 12))
when kd.PCODE = 'P35'
then (SUM(kd.wip_compounding / 1000 / 25.00 / 12))
when kd.PCODE = 'P35M'
then (SUM(kd.wip_compounding / 1000 / 25.60 / 12))
when kd.PCODE = 'P35W'
then (SUM(kd.wip_compounding / 1000 / 25.50 / 12))
when kd.PCODE = 'P38'
then (SUM(kd.wip_compounding / 1000 / 26.70 / 12))
when kd.PCODE = 'P41'
then (SUM(kd.wip_compounding / 1000 / 25.00 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_compounding / 1000 / 18.88 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_compounding / 1000 / 0.00 / 12))
END,
kcd.count_doz_extrusion = CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_extrusion / 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_extrusion / 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P33'
then (SUM(kd.wip_extrusion / 1000 / 25.00 / 12))
when kd.PCODE = 'P35'
then (SUM(kd.wip_extrusion / 1000 / 25.00 / 12))
when kd.PCODE = 'P35M'
then (SUM(kd.wip_extrusion / 1000 / 25.60 / 12))
when kd.PCODE = 'P35W'
then (SUM(kd.wip_extrusion / 1000 / 25.50 / 12))
when kd.PCODE = 'P38'
then (SUM(kd.wip_extrusion / 1000 / 26.70 / 12))
when kd.PCODE = 'P41'
then (SUM(kd.wip_extrusion / 1000 / 25.00 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_extrusion / 1000 / 18.88 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_extrusion / 1000 / 0.00 / 12))
END,
kcd.count_doz_forming = CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_forming / 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_forming / 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P33'
then (SUM(kd.wip_forming / 1000 / 25.00 / 12))
when kd.PCODE = 'P35'
then (SUM(kd.wip_forming / 1000 / 25.00 / 12))
when kd.PCODE = 'P35M'
then (SUM(kd.wip_forming / 1000 / 25.60 / 12))
when kd.PCODE = 'P35W'
then (SUM(kd.wip_forming / 1000 / 25.50 / 12))
when kd.PCODE = 'P38'
then (SUM(kd.wip_forming / 1000 / 26.70 / 12))
when kd.PCODE = 'P41'
then (SUM(kd.wip_forming / 1000 / 25.00 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_forming / 1000 / 18.88 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_forming / 1000 / 0.00 / 12))
END,
kcd.count_doz_deflashing = CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_deflashing / 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_deflashing / 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P33'
then (SUM(kd.wip_deflashing / 1000 / 25.00 / 12))
when kd.PCODE = 'P35'
then (SUM(kd.wip_deflashing / 1000 / 25.00 / 12))
when kd.PCODE = 'P35M'
then (SUM(kd.wip_deflashing / 1000 / 25.60 / 12))
when kd.PCODE = 'P35W'
then (SUM(kd.wip_deflashing / 1000 / 25.50 / 12))
when kd.PCODE = 'P38'
then (SUM(kd.wip_deflashing / 1000 / 26.70 / 12))
when kd.PCODE = 'P41'
then (SUM(kd.wip_deflashing / 1000 / 25.00 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_deflashing / 1000 / 18.88 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_deflashing / 1000 / 0.00 / 12))
END,
kanban_doz = (SUM(count_doz_deflashing)),
virtual_doz = (SUM(count_doz_chemical_weighing + count_doz_compounding + count_doz_extrusion + count_doz_forming)),
total_doz = (SUM(count_doz_chemical_weighing + count_doz_compounding + count_doz_extrusion + count_doz_forming + count_doz_deflashing))
;


I don't know where I can put GROUP BY and also if my query is wrong except in GROUP BY., especially in my CASE Statement.

I attach the data from kanban_data table.

WHERE I need to compute per PCODE.

Thank you

[jassmee]

you may you are used wrong syntax in update query.again recheck your sql query. and when you run the query then always used mysql_error();