Calculation across Tables

[zjwbotak]

Hi all, I am facing difficulties on creating a particular query.

Sample of my data table 1: (DetailsTable)

Order | Material | Quantity | Weight
123 | Orange | 10 |
123 | Apple | 20 |
123 | Guava | 20 |
233 | Orange | 10 |

Sample of my data table 2:(WeightsTable)

Order | Weight |
123 | 100 |
233 | 20 |

How can I create query to update my DetailsTable and allocate the weights proportionately (by quantity) to the individual order's material.

Sample outcome of data table1: (DetailsTable)

Order | Material | Quantity | Weight
123 | Orange | 10 | 20
123 | Apple | 20 | 40
123 | Guava | 20 | 40
233 | Orange | 10 | 20

Where weight of Order 123's Orange = 10/(10+20+20) * 100.

Thank you so much in advance.

Joe

[Rabbit]

If you wanted to store the weights of the individual line items in the first place, why have a weights table at all?

Since this is a calculation, it's better not to store the results of the calculation but to calculate on an as needed basis.

To calculate the individual weights, you can join the two tables and use a subquery to get the sum of the order quantity.

[zjwbotak]

Hi Rabbit,

The reason that the weights table exist is that the items are measured in orders and not individually.

However, during analysis, I would need the weights of individual items hence I have assumed that the weights is proportional to the quantity.

I was wondering if there is a simple query that can assist me in filling up the weights such as:

Update DetailsTable INNER JOIN WeightsTable on DetailsTable.Order= WeightsTable.Order
Set DetailsTable.Weight = WeightsTable.Weight* DetailsTable.Quantity/(SUM(DetailsTable.Quantity)
Group by Order

However, this query gives an error message.

Thank you so much in advance.

[Rabbit]

You'll want to try the subquery instead of the inner join.