Had some free time. I entered the data you sent to me. I tested the code that I posted above. I made one minor change to it. I used standard QUOTES on the WHERE line. ‘COMPLETED’ section. I also used standard QUOTES on the test data you sent to me to install the data in my local MySQL database system.
This gave me the following info from the query above:
Thank you dear Ernie,
No, if you pay attention to the results, it returns duplicate values.
For example, sum(w.wastage_quantity) for the first row of the result must be 54 (not 108). Also the other values are wrong. ( Those are duplicate or sometimes are 4 times).
I don’t know why does it happen?
Thanks for your reply,
Very best regards
You combine three tables using the product line as a line between them. Then, you collect sums of the data. But, you are combining the third table based on the second table, not the first table. A bit confused on the logic you set up for this part. Normally, you want everything based on the first table. Please try this query:
SELECT
SUM(W.WASTAGE_QUANTITY),
SUM(D.DURATION_DOWNTIME),
P.PRODUCTION_DATE,
P.PRODUCT_LINE,
SUM(P.DURATION),
SUM(P.QUANTITY),
SUM(P.NOMINAL_PRODUCTS)
FROM PMT_PRODUCTION_STATISTICS AS P
LEFT JOIN PMT_DOWNTIME AS D
ON P.PRODUCT_LINE = D.LINE AND P.PRODUCTION_DATE = D.DATE_DOWNTIME
LEFT JOIN PMT_WASTE AS W
ON P.PRODUCT_LINE = W.LINE_W AND P.PRODUCTION_DATE = W.DATE_WASTAGE
WHERE P.APP_STATUS = 'COMPLETED'
GROUP BY P.PRODUCT_LINE
There are just two minor changes. This query links the two joins to the first table instead of on each other. I think this should work better for you!
Thanks again,
Unfortunately, it returns the previous result.
For finding the problem of the query, maybe it is better to remove (sum) from that to see all of records which are generated after running the query.
I did it, but I did not understand the problem, probably you can figure it out.
Kind Regards
I am heading to bed, it is late in my part of the world.
I will look into this more in the morning and see if I can figure it out.
It might be the left-join instead of an inner-join issue. I will have to look at it further when I am not so tired!
Sorry I do not have a simple solution for you yet.
Well, Mohamad, my friend, I spent hours on this. Seems that you can not group join’s in that way.
It causes duplicates and so the sum’s will be wrong. I did some research and experiments and found that you can combine multiple SELECT’s as tables and join them to accomplish the results you need.
After studying a lot for you, I came up with this query. You can adjust it to fit your needs better. My version was checked only for the SUM(WASTE_QUANTITY) part. You should check the other totals yourself. Hope this helps!
> SELECT PRODUCTION_DATE, PRODUCT_LINE,
> SUM(DURATION) AS SUM_DUR, SUM(QUANTITY) AS SUM_QUA, SUM(NOMINAL_PRODUCTS) AS SUM_NOM,
> D.SUM_DOWN,
> W.SUM_WAS
> FROM PMT_PRODUCTION_STATISTICS AS P
>
> LEFT JOIN (SELECT LINE, SUM(DURATION_DOWNTIME) AS SUM_DOWN
> FROM PMT_DOWNTIME
> GROUP BY LINE) AS D
> ON P.PRODUCT_LINE = D.LINE
>
> LEFT JOIN (SELECT LINE_W, SUM(WASTAGE_QUANTITY) AS SUM_WAS
> FROM PMT_WASTE
> GROUP BY LINE_W) AS W
> ON P.PRODUCT_LINE = W.LINE_W
>
> WHERE P.APP_STATUS = 'COMPLETED'
> GROUP BY PRODUCT_LINE
I just realized that I did not add the “COMPLETE” section in the WHERE’s. You can add that.
Whew! Hope this works better for you!