CODEWITHSUNDEEP
sqlsql-server
JJ.
JJ.

Reputation: 9950

splitting the values in one column and a set of rows evenly and proportionally among other rows

I know, I confused you.

I have this data:

For the three items that are NULL, I need to:

  1. Add them (1828.94 + 772.90 + 0.00).
  2. Split this total among each ItemID evenly and proportionally based on quantity for each.

Note that there are ItemIDs that are the same but this is OK.

Basically the end result will be the same columns without the 3 rows that have ItemID = NULL and the Amount column will be increased by some because I'm splitting the amount among the ItemIDs.

I'm having a really hard time doing this without having to do a bunch of loops.

Can anyone give me a hand?

Upvotes: 0

Views: 1507

Answers (2)

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

Would this work for what you're trying to do?

DECLARE @NullAmounts MONEY,
        @RowCount INT

SELECT @NullAmounts = SUM(CASE WHEN ItemID IS NULL THEN Amount ELSE 0 END),
       @RowCount = COUNT(*)
FROM Table

UPDATE Table
SET Amount = Amount + (@NullAmounts / @RowCount)
WHERE
    ItemID IS NOT NULL

Of course, after you've run the update, you can DELETE the rows so you don't have them return in a SELECT statement.

DELETE Table
WHERE ItemID IS NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can get the apportioned amount with this query:

select t.*,
       x.AmountToSplit * t.qty / x.TotalQty as AmountToAdd
from t cross join
     (select sum(case when itemId is null then amount end) as AmountToSplit,
             sum(case when itemId is not null then Qty end) as TotalQty
      from t
     ) x
where t.itemId is not null;

If you actually want to update the amounts, then use this as an updatable CTE:

with toupdate as (
      select t.*,
             x.AmountToSplit * t.qty / x.TotalQty as AmountToAdd
      from t cross join
           (select sum(case when itemId is null then amount end) as AmountToSplit,
                   sum(case when itemId is not null then Qty end) as TotalQty
            from t
           ) x
      where t.itemId is not null
     )
update toupdate
    set Amount = Amount + AmountToAdd;

Upvotes: 1

Related Questions