Link to home
Create AccountLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

SQL Query Help

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;


Open in new window

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

Open in new window


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;

Open in new window


Avatar of Zolf
Zolf
Flag of United Arab Emirates image

ASKER

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
User generated image
Avatar of 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
----------------------------------------------------------
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
Avatar of Zolf

ASKER

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?


Avatar of Zolf

ASKER

Thanks for your feedbacks. I will prepare the end result now
Avatar of Zolf

ASKER

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;

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

User generated image
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
Avatar of Zolf

ASKER

The final result I need like so
User generated image
Avatar of 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
Avatar of Zolf

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Zolf

ASKER

cheers!!
Cheers & Thanks.