# 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
jobname  amount    InvAmnt           item#  cost
job1   \$100,000     -               1     \$250
job2   \$50,000      -               2     \$150
job3   \$25,000      -               3     \$100
job4   \$25,000      -               4     \$500``````

so 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
jobname  amount    InvAmnt
job1    \$100,000   \$500
job2    \$50,000    \$250
job3    \$25,000    \$125
job4    \$25,000    \$125``````

how do I accomplish this?

Friday, June 12, 2015 2:27 PM

• Hi shatcher94,

Based on your description, to achieve your requirement, you can reference the below sample.

```CREATE TABLE Table2
(
jobName VARCHAR(5),
amount MONEY,
InvAmnt MONEY
)
;
INSERT INTO Table2(jobName,amount)
VALUES
('job1',100000),
('job2',50000),
('job3',25000),
('job4',25000);

CREATE TABLE Table1
(
item# INT,
cost MONEY
)
;
INSERT INTO Table1
VALUES
(1,250),
(2,150),
(3,100),
(4,500);

;WITH Cte AS
(
SELECT T2.jobName,T2.amount,T2.InvAmnt,ROW_NUMBER() OVER(ORDER BY T2.amount DESC) RN,
cat.totalCost
FROM Table2 T2
CROSS APPLY
(
SELECT SUM(cost) totalCost FROM Table1
) cat
)
SELECT jobName,amount,
CASE WHEN RN=1 THEN 0.5*totalCost
WHEN RN=2 THEN 0.25* totalCost
ELSE 0.125*totalCost
END InvAmnt
FROM Cte

--to update table2
--;WITH Cte AS
--(
--SELECT T2.jobName,T2.amount,T2.InvAmnt,ROW_NUMBER() OVER(ORDER BY T2.amount DESC) RN,
--cat.totalCost
--FROM Table2 T2
--CROSS APPLY
--(
--SELECT SUM(cost) totalCost FROM Table1
--) cat
--)
--UPDATE Cte SET InvAmnt=
--CASE WHEN RN=1 THEN 0.5*totalCost
--	 WHEN RN=2 THEN 0.25* totalCost
--	 ELSE 0.125*totalCost
--END

DROP TABLE Table1,Table2```

If you have any question, feel free to let me know.

Eric Zhang
TechNet Community Support

Monday, June 15, 2015 1:51 AM