4 Replies - 539 Views - Last Post: 20 June 2016 - 12:01 PM

#1 SoundWaves  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Help nesting select statement to SUM properly

Posted 20 June 2016 - 06:59 AM

Hello,
I am providing a scrap analysis report, showing scrap qty, percentage and total cost grouped by part number.

The data is coming from a few different tables and once I introduce my third table the Sum of the total rejects changes. By doing some research it would appear I have to nest my select statement that SUM's the rejects. I am struggling to get this to work with the two Join statements.

Any help is appreciated, let me know if you need more information.

Select Part_Numbers.Part_Number, Part_Numbers.Part_Description, SUM(cast (Rej_Qty as int)) AS Total_Rejects, cast(SUM((Rej_Qty)) as decimal)/ cast(SUM((Acc_Qty))+SUM((Rej_Qty)) as decimal) as Scrap_Percent, 
(SUM(cast (Rej_Qty as int)) * Part_Cost.Cost) as Total_Cost from Lot_Records
                    INNER JOIN Part_Numbers
                    ON Lot_Records.Part_Id=Part_Numbers.Part_Id
                    INNER JOIN Part_Cost
                    ON Part_Numbers.Part_Number=Part_Cost.Part_Number
                    GROUP BY Part_Numbers.Part_Number,Part_Numbers.Part_Description, Cost ORDER BY Total_Cost DESC



Is This A Good Question/Topic? 0
  • +

Replies To: Help nesting select statement to SUM properly

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,635
  • Joined: 16-October 07

Re: Help nesting select statement to SUM properly

Posted 20 June 2016 - 07:22 AM

Hmm... formatting is everything.

First, a quick format with some table aliases:
Select n.Part_Number, 
    n.Part_Description, 
    SUM(cast (Rej_Qty as int)) AS Total_Rejects, 
    cast(SUM((Rej_Qty)) as decimal)/ cast(SUM((Acc_Qty))+SUM((Rej_Qty)) as decimal) as Scrap_Percent, 
    (SUM(cast (Rej_Qty as int)) * c.Cost) as Total_Cost
  from Lot_Records r
    INNER JOIN Part_Numbers n
      ON r.Part_Id=n.Part_Id
    INNER JOIN Part_Cost c
      ON n.Part_Number=c.Part_Number
  GROUP BY n.Part_Number, n.Part_Description, c.Cost
  ORDER BY Total_Cost DESC



Also, Rej_Qty and Acc_Qty don't explictly belong to any table: I'm going to assume Lot_Records. Also, these seem to be the heart of the group by. Perhaps only this:
select Part_Id, 
    SUM(cast (Rej_Qty as int)) AS Total_Rejects, 
    SUM(Acc_Qty) as Acc_Qty
  from Lot_Records
  GROUP BY Part_Id


I don't know why you're casting that to int. It's probably wrong, but we'll keep it.

Oops, Part_Number and Part_Id?!? That's messy. We'll adjust for Part_Number:
select n.Part_Number, 
    SUM(cast (r.Rej_Qty as int)) AS Total_Rejects,
    SUM(r.Acc_Qty) as Acc_Qty
  from Lot_Records r
    INNER JOIN Part_Numbers n
      ON r.Part_Id=n.Part_Id
  GROUP BY n.Part_Number



With that taken out, a more natural query might look like:
Select n.Part_Number, n.Part_Description, 
    r.Total_Rejects, 
    r.Total_Rejects / cast(r.Acc_Qty+r.Total_Rejects as decimal) as Scrap_Percent,
    r.Total_Rejects * c.Cost) as Total_Cost
  from Part_Numbers n
    INNER JOIN Part_Cost c
      ON n.Part_Number=c.Part_Number
    INNER JOIN (
      select n.Part_Number, 
          SUM(cast (r.Rej_Qty as int)) AS Total_Rejects,
          SUM(r.Acc_Qty) as Acc_Qty
        from Lot_Records r
          INNER JOIN Part_Numbers n
            ON r.Part_Id=n.Part_Id
        GROUP BY n.Part_Number
      ) r ON r.Part_Number=n.Part_Number
  ORDER BY Total_Cost DESC



I'm still dubious about that Part_Number/Part_Id things. Any anomalies in your results probably relate to that, so I'd take a closer look.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 SoundWaves  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Re: Help nesting select statement to SUM properly

Posted 20 June 2016 - 07:48 AM

Thanks for your help so far.
First a few explanations.

I did remove the Cast portion of the query, not sure why I had that in my post.

As far as the tables go, here is a simple snap shot.

Part_Numbers
- Part_Id
- Part_Number
- Part_Description

Lot_Records
- Part_Id
- Rej_Qty
- Acc_Qty

Part_Cost
- Part_Number
- Cost

Note, there is no Part_Id in the Part_Cost Table
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,635
  • Joined: 16-October 07

Re: Help nesting select statement to SUM properly

Posted 20 June 2016 - 09:12 AM

Ok, prior post looks like it still applies.

Using:
select n.Part_Number, SUM(r.Rej_Qty) AS Rej_Qty, SUM(r.Acc_Qty) as Acc_Qty
  from Lot_Records r
    INNER JOIN Part_Numbers n
      ON r.Part_Id=n.Part_Id
  GROUP BY n.Part_Number




Turns
Lot_Records
- Part_Id
- Rej_Qty
- Acc_Qty



Into:
(subquery)
- Part_Numbers
- Rej_Qty
- Acc_Qty



Which is what you need to move forward.

I question not using Part_Id universally. Or, having a separate table for costs.
Was This Post Helpful? 0
  • +
  • -

#5 SoundWaves  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Re: Help nesting select statement to SUM properly

Posted 20 June 2016 - 12:01 PM

The reason for no Part_Id in the last table is a maintenance thing. The cost table is separated and purchasing dumps data from excel to fill the table, so there is no maintenance or editing.

I am unable to get your query to work as-is SQL doesn't recognize field once the whole statement is put together, I will continue to work at it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1