SQL Query Help

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

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

----------------------------------------------------------
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.

----------------------------------------------------------
MySQL's non-standard grouping:

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.

select s.receiptno,  s.productid,   ir.rawmaterialid,     s.dateofentry,     cm.name,      p.name,    s.quantity,    inf.quantity,    f.rawmaterialid,    rm.name,    (f.quantity * s.quantity) ,    ir.supplierid,

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

select s.receiptno,  s.productid,   ir.rawmaterialid,     s.dateofentry,     cm.name,      p.name,    s.quantity,    inf.quantity,    f.rawmaterialid,    rm.name,    (f.quantity * s.quantity) ,    ir.supplierid,
...
GROUP 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) ,    ir.supplierid,

and then see how many more rows are returned.

----------------- 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

dump-hakimanteb-202105041032.sql

Thanks for the data Zolf.

If anyone else find this helpful please see that data online here:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ce3814c525e2cd123a67d1875c76ef6f

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?

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_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,     s.dateofentry,     cm.name,      p.name,    s.quantity,    inf.quantity,    f.rawmaterialid,    rm.name,    (f.quantity * s.quantity)
order by
   dateofentry,
   productid,
   rawmaterialid;

Select allOpen in new window

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

The final result I need like so

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

For an online demo of the above you may go here:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=37ed6e028c21facb9a30db6323daa28c&hide=255

cheers!!

Cheers & Thanks.