SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Subquery?
    Is it after y case statement?

    Thank you

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    Subquery?
    yes, subquery

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

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please see post #12
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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