Problem in GROUP BY in UPDATE with CASE

**newphpcoder** · Mar 12, 2012 03:57

Hi.

I got an error:

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

in my query:

Code:

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** · Mar 12, 2012 04:02

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** · Mar 12, 2012 17:54

Originally Posted by r937

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 kanban_data I have more than one PCODE.

So i need to sum per PCODE and save to kanban_checker_doz.

Like for example I have
pCODE wip_chemicalweighing
P35 10.00
P35 25.00
P35 15.00
P35M 50.00
P35M 35.00
P35M 40.00

So I need to sum them per PCODE.

Where I put group by?

Thank you

**r937** · Mar 12, 2012 18:04

Originally Posted by newphpcoder

Where I put group by?

in the subquery i mentioned

**newphpcoder** · Mar 12, 2012 18:08

Subquery?
Is it after y case statement?

Thank you

**r937** · Mar 12, 2012 18:13

Originally Posted by newphpcoder

Subquery?

yes, subquery

please read post #2 more carefully

**newphpcoder** · Mar 12, 2012 18:17

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** · Mar 12, 2012 18:38

You mean like this:

Code:

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** · Mar 12, 2012 19:13

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

Code:

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

**newphpcoder** · Mar 12, 2012 19:13

I revise my query:

Code:

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** · Mar 12, 2012 20:25

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

Code:

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** · Mar 13, 2012 03:03

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** · Mar 13, 2012 03:09

Thank you..

It works using this code:

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

Code:

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** · Mar 13, 2012 03:16

please see post #12

User Tag List

Thread: Problem in GROUP BY in UPDATE with CASE

Thread Tools

Display

Problem in GROUP BY in UPDATE with CASE

Bookmarks

Bookmarks

Posting Permissions