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_rawmaterialQtyfrom finishproductprofile finner join sendtocompanyfromcontractmfg s on f.productid = s.productidinner join inventory_finishedproduct inf on inf.productid = f.productidinner join raw_material rm on f.rawmaterialid = rm.rawmaterialidinner join product p on f.productid = p.productidinner join inventory_rawmaterial ir on f.rawmaterialid = ir.rawmaterialidinner join contract_manufacturer cm on s.contractmfgid = cm.contractmfgidwhere s.isdelete = 0group by s.receiptno, s.productid, ir.rawmaterialidorder 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 iwrinner join raw_material rm on iwr.rawmaterialid = rm.rawmaterialidwhere iwr.isdelete = 0group by rm.rawmaterialidorder 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 wasteQtyfrom finishproductprofile finner join sendtocompanyfromcontractmfg s on f.productid = s.productidinner join inventory_finishedproduct inf on inf.productid = f.productidinner join raw_material rm on f.rawmaterialid = rm.rawmaterialidinner join product p on f.productid = p.productidinner join inventory_rawmaterial ir on f.rawmaterialid = ir.rawmaterialidinner join contract_manufacturer cm on s.contractmfgid = cm.contractmfgidinner join inventory_waste_rawmaterial invwaste on invwaste.rawmaterialid = rm.rawmaterialidwhere s.isdelete = 0order by s.dateofentry, s.productid, f.rawmaterialid;
I have attached the output of the last query but as you can see I need to sum the inventory_rawmaterialQty and rejectedQty column to show the their total quantity in the warehouse. 20210503-123542.xlsx
Below is the output of the first query which does not contain the rejectedQty column
arnold
Your likely issue is the preceding groupings split the sum of rawmaterial among other items.
Without data it is hard.
The criteria in the second aggregates in total
The first query
Receiptno,productid will breakup the materialid sum.
I.e. Receiptno and productid dictate the the materialid reference..
Meaning based on receipt and productid you get the sum based on materialid
PortletPaul
---------------------------------------------------------- As a general rule, don't try to sum() across a join or multiple joins. Instead use subqueries to do the SUM() then join those subqueries as "derived tables". ----------------------------------------------------------
With each join you run the risk of multiplying the rows, plus, because you are using inner joins, you are also running the risk of ignoring rows if matching data does not exist in all of the tables.
For example, what happens if there is a rawmaterialid but no waste material as yet?
from finishproductprofile f inner join raw_material rm on f.rawmaterialid = rm.rawmaterialid inner join inventory_waste_rawmaterial invwaste on invwaste.rawmaterialid = rm.rawmaterialid
If there is no rows in the inventory_waste_rawmaterial for a particular rawmaterilid then that rawmaterialid will be ignored by the query.
Alternatively, if there are MORE than ONE row of wasted material (e.g. perhaps wasted one row for waste on last Monday and another for waste last Tuesday) then when attempting to SUM anything in the query you run the risk of that sum being incorrect because of the row multiplication(s) involved.
group by s.receiptno, s.productid, ir.rawmaterialid
I am also a bit concerned that because you are using MySQL that you might not be using ONLY_FULL_GROUP_BY and because of this it makes it appear that you can group by just a few columns, but still accurately output several other columns not mentioned in the group by clause. This is absolutely untrue and something to avoid.
Some of the extra columns (not in bold) MIGHT be "functionally dependent", but to be honest I doubt they all are and therefore I'm not confident this query is producing accurate results.
In standard SQL the group by clause should include ALL "non-aggregating" columns, so try it with
----------------- sample data and expected result ----------------
the best way to help you is to have some access to the tables involved - with data
Please don't provide the result set of a multiple table join that does not meet your needs. If you really want working code we need SAMPLE data per table so that we can actually run a query involving all of the tables
sample data does not have to be huge, it can omit private data or have private data replaced by something innocuous BUT the samples should allow joins to be performed and the overall query should produce some rows
Thanks for all your comments and specially PortletPaul for elaborating in details the issues. I have prepared mock data, please let me know if anything is ambiguous
What we really need to now know if "what is the correct result"? (tip, this is why "sample data" typically is very small, because you need to manually work out the wanted result)
In other words what columns do you really need in the final result, but also what are the correct aggregated numbers
for example: you have s.dateofentry in the select list and group by, so does this mean you want waste ON THAT SAME DAY?
Zolf
ASKER
Thanks for your feedbacks. I will prepare the end result now
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_rawmaterialQtyfrom finishproductprofile finner join sendtocompanyfromcontractmfg s on f.productid = s.productidinner join inventory_finishedproduct inf on inf.productid = f.productidinner join raw_material rm on f.rawmaterialid = rm.rawmaterialidinner join product p on f.productid = p.productidinner join inventory_rawmaterial ir on f.rawmaterialid = ir.rawmaterialidinner join contract_manufacturer cm on s.contractmfgid = cm.contractmfgidwhere s.isdelete = 0GROUP BY s.receiptno, s.productid, ir.rawmaterialid, s.dateofentry, cm.name, p.name, s.quantity, inf.quantity, f.rawmaterialid, rm.name, (f.quantity * s.quantity)order by dateofentry, productid, rawmaterialid;
The above query returns the correct set of data. I have added all the select column in the Group by Except the supplierid. The reason is I want to SUM the quantity based on the rawmaterial id. There are cases where 2 suppliers can have the same raw material and in the report I want to just get the available qty of the rawmaterial without knowing it belongs to which supplier. That is the reason I did not add supplierid to the group by.
Now I also want to show another column called wasteqty which will show the waste qty next to the rawmaterial. If you see the availableqty column, it is showing me the rawmaterial available in the inventory, I need this for wasteqty
Zolf
ASKER
The final result I need like so
Zolf
ASKER
In the above screenshot I noticed the value in the wasteQty column I made a mistake for the first record. It should have been 895 instead of 128 i.e. the qty not its id
you have s.dateofentry in the select list and group by, so does this mean you want waste ON THAT SAME DAY?
We need to concentrate on the rawmaterialid, so the wasteqty amount will show multiple times in the result for rawmaterialid. Taking the above e.g. for rawmaterialid 128, the wasteqty amount 895 will show each time for receiptno's that have rawmaterialid 128
20210503-123542.xlsx
Below is the output of the first query which does not contain the rejectedQty column