Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2015
    Posts
    3

    Cool Unanswered: duplicates of part.num to be summed and add another column called user

    There are 3 columns part num, Qty and MO. Each MO has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.

    1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.

    Heres the code,

    part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,

    (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid

    LEFT JOIN wo ON moitem.id = wo.moitemid

    LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about some sample data for each of you tables. then sample results from that data and we should be able to help you out a bit. Because I admit I am a little lost with your explanation.
    Dave

Tags for this Thread

Posting Permissions

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