SQL Server SUM (add) prices of accessories to products

Question

I need to sum up the prices of accessories to the main product. There is no link between the accessories and the correspondent product, however all accessories between two product belongs to the previous product (see side note).

SQL Server 2017

Input:

| No | Order | Type      | ProdNo | Price |     side note
--------------------------------------
|  1 | 20213 | Product   | 1320   | 200   |     + 0 + 20
|  2 | 20213 | Accessory | 823    | 0     |     acc. of 1320
|  3 | 20213 | Accessory | 836    | 20    |     acc. of 1320
|  4 | 20213 | Product   | 2680   | 300   |     + 0 + 0 + 0 + 0
|  5 | 20213 | Accessory | 231    | 0     |     acc. of 2680
|  6 | 20213 | Accessory | 536    | 0     |     acc. of 2680
|  7 | 20213 | Accessory | 23     | 0     |     acc. of 2680
|  8 | 20213 | Accessory | 361    | 0     |     acc. of 2680
|  9 | 20213 | Product   | 3320   | 50    |     + 10 + 15
| 10 | 20213 | Accessory | 328    | 10    |     acc. of 3320 
| 11 | 20213 | Accessory | 369    | 15    |     acc. of 3320

Output:

| No | Order | Type      | ProdNo | Price |  
--------------------------------------
|  1 | 20213 | Product   | 1320   | 220   |
|  4 | 20213 | Product   | 2680   | 300   |
|  9 | 20213 | Product   | 3320   | 75    |

"There is no link between the accessories and the correspondent product" -- That calls for changing the schema and create such links. — sticky bit, Mar 25 '20 at 10:28

score 2 · Accepted Answer · answered Mar 25 '20 at 10:12

I understand this as a kind of gaps-and-island problem. You could use window functions as follows to solve it:

select *
from (
    select no, order, type, prodNo, sum(price) over(partition by grp) price
    from (
        select
            t.*, 
            sum(case when type = 'Product' then 1 else 0 end) 
                over(partition by orderNo order by no) grp
        from mytable t
    ) t
) t
where type = 'Product'

The most inner query uses a window sum to define the groups of records. Everytime a product record is met, a new group starts. The intermediate query sums the prices in each group. Finally, the most outer query filters on product records only.

score 2 · Answer 2 · answered Mar 25 '20 at 10:40

Assign the groups by summing up the number of products before each row. Then aggregate:

select order, 'Product' as type, 
       max(case when type = 'Product' then prodno end) as prodno,
       sum(price)
from (select t.*,
             sum(case when type = 'Product' then 1 else 0 end) over (partition by order order by no) as grp
      from t
     ) t
group by grp, order

SQL Server SUM (add) prices of accessories to products

2 Answers2