Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: 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:

    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
    Attached Thumbnails Attached Thumbnails sample.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    Where I put group by?
    in the subquery i mentioned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    Sub query?

    In my case statement?

    Thank you

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not in your CASE expression

    please read post #2 more carefully
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    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

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2010
    Posts
    136
    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

  11. #11
    Join Date
    Dec 2010
    Posts
    136
    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

  12. #12
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    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 ...
    Is it what yuo mean:

    Code:
    UPDATE kanban_checker_doz kcd  
    INNER JOIN (SELECT PCODE, SUM(wip_chemicalweighing) AS wip_chemicalweighing, SUM(wip_compounding) AS wip_compounding FROM kanban_data GROUP BY PCODE ) kd
    ON kcd.PCODE = kd.PCODE
    SET count_doz_chemical_weighing = 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,
    count_doz_compounding = 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;
    i got an error:

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



    Thank you

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2010
    Posts
    136
    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

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am getting really tired of the identical thread on two different sites

    i'm leaving this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •