CODEWITHSUNDEEP
mysql
El Tigre
El Tigre

Reputation: 182

Accurate results joining 2 tables

Table A lists an item and the number of pieces that must go through each of the 4 possible stations.

Item      To Cut      To Paste      To Laminate      To Ship
Box          9            9              5              9
Cart         1            0              10             10

Table B lists the number of pieces that have been processed through each of the stations.

Item      Cut Done      Paste Done      Laminated      Shipped
Box          9            0                0             0
Box          0            9                0             0
Box          0            0                5             0
Box          0            0                0             9

When I join them it shows:

Item      To Cut      Cut Done      To Paste      Paste Done
Box          36          9             36             9

This is because of the way Table B works. How can I get it to accurately show the total To Cut and Cut Done in one table so percentages are correct?

My query is:

select a.item, a.to_cut, a.to_paste, a.to_laminate, a.to_ship, b.cut_done, b.paste_done, b.laminated, b.shipped

from ToDo a join Done b on a.item = b.item

Upvotes: 0

Views: 26

Answers (1)

McNets
McNets

Reputation: 10807

You should add a subquery with the calculated sums.

select tableA.Item, To_Cut, To_Paste, To_Laminate, To_Ship, d.Cut_Done, d.Paste_Done, d.Laminated, d.Shipped
from tableA 
inner join (select Item, 
                   sum(Cut_Done) as Cut_Done,
                   sum(Paste_Done) as Paste_Done,
                   sum(Laminated) as Laminated,
                   sum(Shipped) as Shipped
            from   tableB
            group by Item) d
on tableA.Item = d.Item;

| Item | To_Cut | To_Paste | To_Laminate | To_Ship | Cut_Done | Paste_Done | Laminated | Shipped |
|-----:|--------|----------|-------------|---------|----------|------------|-----------|---------|
| Box  | 9      | 9        | 5           | 9       | 9        | 9          | 5         | 0       |

Rextester here

Upvotes: 1

Related Questions