Problem in GROUP BY in UPDATE with CASE

newphpcoder · March 12, 2012, 9:57am

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

r937 · March 12, 2012, 10:02am

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

newphpcoder · March 12, 2012, 11:54pm

r937 · March 13, 2012, 12:04am

in the subquery i mentioned

newphpcoder · March 13, 2012, 12:08am

Subquery?
Is it after y case statement?

Thank you

r937 · March 13, 2012, 12:13am

yes, subquery

please read post #2 more carefully

newphpcoder · March 13, 2012, 12:17am

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

newphpcoder · March 13, 2012, 12:38am

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

r937 · March 13, 2012, 1:13am

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 ...

newphpcoder · March 13, 2012, 1:13am

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

newphpcoder · March 13, 2012, 2:25am

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

r937 · March 13, 2012, 9:03am

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

newphpcoder · March 13, 2012, 9:09am

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

r937 · March 13, 2012, 9:16am

please see post #12

Topic		Replies	Views
Wrong sum output from case statement condition Databases	4	747	October 8, 2014
Mysql count problem Databases	12	1357	September 24, 2014
SQL Server - GROUP BY clause Databases	17	2008	September 20, 2014
Problem with subquery Databases	19	1052	October 8, 2014
Problem with jointing via UNION , UNION ALL Databases	21	1171	February 3, 2010

Problem in GROUP BY in UPDATE with CASE

Related topics