troubleshooting Question

SQL Query Help

Avatar of zolf
zolfFlag for United Arab Emirates asked on
SQLDatabasesMySQL Server
14 Comments1 Solution32 ViewsLast Modified:
Hi there,

I have this query which returns the required data

select
   s.dateofentry,
   s.receiptno,
   cm.name as mfg,
   s.productid,
   p.name as product,
   s.quantity as manufacturedQty,
   inf.quantity as availableQty,
   f.rawmaterialid,
   rm.name as rawmaterial,
   (f.quantity * s.quantity) as usedQuantity,
   ir.supplierid,
   ir.rawmaterialid as inventory_rawmaterialID,
   SUM(ir.quantity) as inventory_rawmaterialQty
from
   finishproductprofile f
inner join sendtocompanyfromcontractmfg s on
   f.productid = s.productid
inner join inventory_finishedproduct inf on
   inf.productid = f.productid
inner join raw_material rm on
   f.rawmaterialid = rm.rawmaterialid
inner join product p on
   f.productid = p.productid
inner join inventory_rawmaterial ir on
   f.rawmaterialid = ir.rawmaterialid
inner join contract_manufacturer cm on
   s.contractmfgid = cm.contractmfgid
where
   s.isdelete = 0
group by
   s.receiptno,
   s.productid,
   ir.rawmaterialid
order by
   dateofentry,
   productid,
   rawmaterialid;


Now I want to add another data column to this query, so I get SUM of rejectedQty of rawmaterial but for some reason I am not getting the correct amount.

This is the query which gives me the sum of the rejectedQty

select
    rm.rawmaterialid,
    rm.name,
    SUM(iwr.quantity)
from
    inventory_waste_rawmaterial iwr
inner join raw_material rm on
    iwr.rawmaterialid = rm.rawmaterialid
where iwr.isdelete = 0
group by 
   rm.rawmaterialid
order by rm.name

The below query gives me all the columns but then I am not able to get the SUM of the 
SUM(ir.quantity) and
SUM(iwr.quantity)
select
    s.dateofentry,
    s.receiptno,
    s.productid,
    inf.productid as pid,
    p.name as product,
    s.quantity as manufacturedQty,
    inf.quantity as availableQty,
    f.rawmaterialid,
    rm.name as rawmaterial,
    (f.quantity * s.quantity) as usedQuantity,
    ir.supplierid,
    ir.rawmaterialid as inventory_rawmaterialID,
    ir.quantity as inventory_rawmaterialQty,
   invwaste.quantity as wasteQty
from
    finishproductprofile f
inner join sendtocompanyfromcontractmfg s on
    f.productid = s.productid
inner join inventory_finishedproduct inf on
    inf.productid = f.productid
inner join raw_material rm on
    f.rawmaterialid = rm.rawmaterialid
inner join product p on
    f.productid = p.productid
inner join inventory_rawmaterial ir on
    f.rawmaterialid = ir.rawmaterialid
inner join contract_manufacturer cm on
   s.contractmfgid = cm.contractmfgid
inner join inventory_waste_rawmaterial invwaste on 
   invwaste.rawmaterialid = rm.rawmaterialid
where
    s.isdelete = 0
order by
    s.dateofentry,
    s.productid,
    f.rawmaterialid;

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros