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();