Jump to content

Problem in GROUP BY in UPDATE with CASE

newphpcoder

By newphpcoder
in MySQL Help

Recommended Posts

newphpcoder Advanced Member

newphpcoder

Posted
Posted

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

post-101569-13482403311843_thumb.jpg

Link to comment
https://forums.phpfreaks.com/topic/258722-problem-in-group-by-in-update-with-case/
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

ok, as far as I know you can't use GROUP BY in an update, also there is no GROUP BY in that code that I can see, and on another note, that's some seriously tedious CASE work, it looks a lot like you would be better making a new refference table for that info and selecting the values you need from that.

newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

You mean I need to have another table for the sum of data from kanban_data?

 

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

 

newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

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

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.