Problem in GROUP BY in UPDATE with CASE

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

why are you using SUM? is there more than one kd.PCODE for each kcd.PCODE?

if so, you will want to join each kcd.PCODE to a subquery where the SUMs are aggregated with a GROUP BY on kd.PCODE

in the subquery i mentioned

Subquery?
Is it after y case statement?

Thank you

yes, subquery

please read post #2 more carefully

You mean in this code:

UPDATE kanban_checker_doz kcd JOIN kanban_data kd ON (kcd.PCODE = kd.PCODE)

Thank you…

Sorry I don’t understand what subquery you mean

You mean like this:


UPDATE kanban_checker_doz kcd  SET kcd.count_doz_chemical_weighing = (select 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)) end FROM kanban_data kd GROUP BY kd.PCODE;

but I got an error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1
(0 ms taken)

Thank you

in post #2, i said you need to join to the subquery

UPDATE kanban_checker_doz kcd
INNER JOIN ([I] subquery here [/I]) kd
ON [I]join-conditions[/I]
SET ...

I revise my query:


UPDATE kanban_checker_doz SET count_doz_chemical_weighing = (SELECT 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 FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_compounding = (SELECT 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 FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_extrusion = (SELECT 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 FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_forming = (SELECT 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 FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_deflashing = (SELECT 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 FROM kanban_data kd GROUP BY kd.PCODE),
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))
;

and I got an error:

Error Code : 1111
Invalid use of group function
(0 ms taken)
and I think this error:
came from this part:

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 tried this:
kanban_doz = ( SELECT SUM(count_doz_deflashing) FROM kanban_checker_doz GROUP BY PCODE)
but the error :

Error Code : 1093
You can’t specify target table ‘kanban_checker_doz’ for update in FROM clause
(15 ms taken)

Thank you

When I tried it first for testing the subquery in case statment:


UPDATE kanban_checker_doz SET count_doz_chemical_weighing = (SELECT CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_chemicalweighing * 1000 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_chemicalweighing * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_chemicalweighing * 1000 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_chemicalweighing * 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_chemicalweighing * 1000 / 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 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_chemicalweighing * 1000 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_chemicalweighing * 1000 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_chemicalweighing * 1000 / 12))
END FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_compounding = (SELECT CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_compounding * 1000 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_compounding * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_compounding * 1000 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_compounding * 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_compounding * 1000 / 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 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_compounding * 1000 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_compounding * 1000 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_compounding * 1000 / 12))
END FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_extrusion = (SELECT CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_extrusion * 1000 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_extrusion * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_extrusion * 1000 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_extrusion * 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_extrusion * 1000 / 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 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_extrusion * 1000 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_extrusion * 1000 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_extrusion * 1000 / 12))
END FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_forming = (SELECT CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_forming * 1000 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_forming * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_forming * 1000 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_forming * 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_forming * 1000 / 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 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_forming * 1000 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_forming * 1000 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_forming * 1000 / 12))
END FROM kanban_data kd GROUP BY kd.PCODE),
count_doz_deflashing = (SELECT CASE
when kd.PCODE = 'P27'
then (SUM(kd.wip_deflashing * 1000 / 12))
when kd.PCODE = 'P28'
then (SUM(kd.wip_deflashing * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_deflashing * 1000 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_deflashing * 1000 / 25.10 / 12))
when kd.PCODE = 'P32W'
then (SUM(kd.wip_deflashing * 1000 / 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 / 12))
when kd.PCODE = 'P45'
then (SUM(kd.wip_deflashing * 1000 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_deflashing * 1000 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_deflashing * 1000 / 12))
END FROM kanban_data kd GROUP BY kd.PCODE)
;

I got an error:

Error Code : 1242
Subquery returns more than 1 row
(0 ms taken)

what is it mean?what’s wrong in my statement.

Thank you

concentrate on the subquery first

the subquery should be a query, by itself, that can return one row per PCODE, and contain all the SUMs

Thank you…

It works using this code:


UPDATE kanban_checker_doz kcd SET count_doz_chemical_weighing = (SELECT CASE
when kd.PCODE = 'P28'
then (SUM(kd.wip_chemicalweighing * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_chemicalweighing * 1000 / 25.10 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_chemicalweighing * 1000 / 25.50 / 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.25 / 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 / 24.80 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_chemicalweighing * 1000 / 18.90 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_chemicalweighing * 1000 / 25.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_chemicalweighing * 1000 / 27.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_chemicalweighing * 1000 / 27.60 / 12))
END FROM kanban_data kd WHERE kcd.PCODE = kd.PCODE GROUP BY kd.PCODE),
count_doz_compounding = (SELECT CASE
when kd.PCODE = 'P28'
then (SUM(kd.wip_compounding * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_compounding * 1000 / 25.10 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_compounding * 1000 / 25.50 / 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.25 / 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 / 24.80 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_compounding * 1000 / 18.90 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_compounding * 1000 / 25.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_compounding * 1000 / 27.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_compounding * 1000 / 27.60 / 12))
END FROM kanban_data kd WHERE kcd.PCODE = kd.PCODE  GROUP BY kd.PCODE),
count_doz_extrusion = (SELECT CASE
when kd.PCODE = 'P28'
then (SUM(kd.wip_extrusion * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_extrusion * 1000 / 25.10 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_extrusion * 1000 / 25.50 / 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.25 / 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 / 24.80 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_extrusion * 1000 / 18.90 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_extrusion * 1000 / 25.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_extrusion * 1000 / 27.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_extrusion * 1000 / 27.60 / 12))
END FROM kanban_data kd WHERE kcd.PCODE = kd.PCODE  GROUP BY kd.PCODE),
count_doz_forming = (SELECT CASE
when kd.PCODE = 'P28'
then (SUM(kd.wip_forming * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_forming * 1000 / 25.10 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_forming * 1000 / 25.50 / 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.25 / 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 / 24.80 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_forming * 1000 / 18.90 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_forming * 1000 / 25.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_forming * 1000 / 27.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_forming * 1000 / 27.60 / 12))
END FROM kanban_data kd WHERE kcd.PCODE = kd.PCODE  GROUP BY kd.PCODE),
count_doz_deflashing = (SELECT CASE
when kd.PCODE = 'P28'
then (SUM(kd.wip_deflashing * 1000 / 22.00 / 12))
when kd.PCODE = 'P30'
then (SUM(kd.wip_deflashing * 1000 / 25.10 / 12))
when kd.PCODE = 'P32'
then (SUM(kd.wip_deflashing * 1000 / 25.50 / 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.25 / 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 / 24.80 / 12))
when kd.PCODE = 'P42'
then (SUM(kd.wip_deflashing * 1000 / 18.90 / 12))
when kd.PCODE = 'P43'
then (SUM(kd.wip_deflashing * 1000 / 25.00 / 12))
when kd.PCODE = 'P46'
then (SUM(kd.wip_deflashing * 1000 / 27.00 / 12))
when kd.PCODE = 'P47'
then (SUM(kd.wip_deflashing * 1000 / 27.60 / 12))
END FROM kanban_data kd WHERE kcd.PCODE = kd.PCODE  GROUP BY kd.PCODE);


UPDATE kanban_checker_doz SET
virtual_doz = (count_doz_chemical_weighing + count_doz_compounding + count_doz_extrusion
+ count_doz_forming),
total_doz = (count_doz_chemical_weighing + count_doz_compounding + count_doz_extrusion
+ count_doz_forming + count_doz_deflashing)

but on my second update I notice that when I only have data in count_doz_chemical_weighing and the other is NULL the update did not work the virtual_doz still NULL.I want to read the data from count_doz_chemical_weighing…

Thank you

please see post #12