how to sum a field and distribute the sum between records in another table

Question

I have a table whose records consist of minor inventory. I need to sum the cost of these records and distribute the sum in another table based on the price of the record. for example. say the sum of the inventory is is $1000, and I have 4 jobs in the other table.

         *table 2*                    *table 1*
**job#  amount    InvAmnt**       **item#  cost**
  job1  $100,000     -               1     $250
  job2  $50,000      -               2     $150
  job3  $25,000      -               3     $100
  job4  $25,000      -               4     $500

I need to sum everything in table 1 then based on the value of the amount in table 2. therefore, 50% of the sum would go to job1, 25% of the sum would go to job2, and 12.5% of the sum would go to job3 and job4. So the final table should look like this:

         *table 2*          
**job#  amount    InvAmnt**  
  job1  $100,000   $500
  job2  $50,000    $250
  job3  $25,000    $125
  job4  $25,000    $125

how do I accomplish this?

Answer 1

You can sum the items in each table to get the total sum and then cross join both aggregate results to table2:

UPDATE     table2
SET        InvAmnt = sum1 * InvAmnt / sum2
FROM       table2
CROSS JOIN (SELECT SUM(cost) AS sum1 FROM table1) t
CROSS JOIN (SELECT SUM(InvAmnt) AS sum2 FROM table2) s