SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Threaded View

  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


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
  •