CODEWITHSUNDEEP
sqlsql-serversql-server-2008
M.devries
M.devries

Reputation: 9

update sum of records within the same table (reducing mutple records to one)

We have multiple records with the same key (Item) and we would like to reduce that to one record with the sum of all records using an update. The target record is one of those records. Also we would like to do that over multiple items. example (Original Image):

+ -------- + --------- + ---- + ------ + ---- + ------- + ------------ + ------ + ------ + ------- + ----------- + ------- + --------- +
| Location | Financial | Real | Orders | Free | Ordered | Average Sale | Result | Result | StockNo | Stock Costs | Value   | Warehouse |
+ -------- + --------- + ---- + ------ + ---- + ------- + ------------ + ------ + ------ + ------- + ----------- + ------- + --------- +
| 106      | 84        | 0    | 45     | -45  | 0       | 0            | 84     | 0      | 0000000 | 50.22       | 0.00    | 0         |
| 106      | 0         | 4    | 0      |  4   | 0       | 0            | 0      | 4      | 1004424 | 50.22       | 200.88  | 0         |
| 106      | 0         | 80   | 0      | 80   | 0       | 0            | 0      | 80     | 0000001 | 50.22       | 4017.60 | 0         |
+ -------- + --------- + ---- + ------ + ---- + ------- + ------------ + ------ + ------ + ------- + ----------- + ------- + --------- +

I already tried:

update Itemstock 
    Set queuequantity       = (Select Sum(IS_2.queuequantity)       from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        reservationquantity = (Select Sum(IS_2.reservationquantity) from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        warehousequantity   = (Select Sum(IS_2.warehousequantity)   from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        averageout          = (Select Sum(IS_2.averageout)          from itemstock IS_2 where (IS_2.item=item) and bookyear=2017),
        purchasequantity    = (Select Sum(IS_2.purchasequantity)    from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        financialamount     = (Select Sum(IS_2.financialamount)     from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        financialresult     = (Select Sum(IS_2.financialresult)     from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        technicalquantity   = (Select Sum(IS_2.technicalquantity)   from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        technicalamount     = (Select Sum(IS_2.technicalamount)     from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017),
        technicalresult     = (Select Sum(IS_2.technicalresult)     from itemstock IS_2 where (IS_2.item=item) and IS_2.bookyear=2017)
    where item=(select objectid from item where Itemgroup=15900239)
        and Stockno=0
        and bookyear=2017`

However that gives the following error:

multiple rows in singleton select

I understand why, but can't get to a solution. I'm not an SQL expert.

Upvotes: 0

Views: 171

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Instead of all those correlated subquery, try using aggregation once and do the update on join. Also, use IN in the where clause as the subquery may return more than one row:

update a
set a.queuequantity = b.queuequantity,
    a.reservationquantity = b.reservationquantity,
    ...
from itemstock a
join (
    select item,
        Sum(queuequantity) as queuequantity,
        Sum(reservationquantity) as reservationquantity,
        ...
    from itemstock
    where bookyear = 2017
    group by item
    ) b on a.item = b.item
where a.item in (
        select objectid
        from item
        where Itemgroup = 15900239
        )
    and a.Stockno = 0
    and a.bookyear = 2017

Upvotes: 2

Related Questions