I have a list of products and components, that have different prices. Here is a simplified example.

drop table if exists #group_test
Create Table #group_test
    (
    ID Integer
    , Hierarchy Nvarchar(200)
    , Price Integer
    )

Insert Into #group_test
values 
    (1,'001',10)
    , (2,'001.001',20)
    , (3,'001.002',5)
    , (4,'001.002.001',3)
    , (5,'001.002.002',2)
    , (6,'001.002.003',4)
    , (7,'001.003',6)

The ID column is the ID of the product or component. The hierarchy shows what you need to build a product. For example product 1 consists of:

• Component 2
• Product 3
• Component 7

Product 3 consists of:

• Component 4
• Component 5
• Component 6

Now I want to sum the price of every product or component and all its subentities. This is my desired result:

I achieved the desired result with a subquery. But since the table is very big I have the feeling that this is very inefficient.

Select 
ID
, Hierarchy
, Price
, (
    Select sum(Price)
    From #group_test as in_tb
    where in_tb.Hierarchy like Left(out_tb.Hierarchy, 3 4*(Len(out_tb.Hierarchy)-Len(Replace(out_tb.Hierarchy,'.',''))))   '%'
) as Total_Price 
From #group_test as out_tb

I wanted to use an over (partition by) but that didn't work:

Select 
ID
, Hierarchy
, Price
, sum(Price) 
    over (partition by Left
            (
                Hierarchy
                , 3 4*
                (
                Len(Hierarchy)-
                Len(Replace(Hierarchy,'.',''))
                )
            )
        )
as Total_Price
From #group_test

Is there a way how I can use over (partition by) to achieve the same result as with my subquery? Or do you know any other methods that are efficient and easy to read?

CodePudding user response：

I'm sorry - the answer to this is going to be more simple because of the way you have the data set up.

By the way - I'm assuming all levels in the hierarchy can only go from 000 to 999 - no hierarchies like 001.11111.002 - it becomes a bit more complex if that is the case.

However, because each of the levels has its own unique price associated with it, you can simply add the price of all the children (including children of children etc) to get the total.

See below and this db<>fiddle for example code

SELECT   out_tb.ID,
         out_tb.Hierarchy,
         out_tb.Price,
         SUM(in_tb.Price) AS Total_Price
FROM     #group_test AS out_tb
         INNER JOIN #group_test AS in_tb ON in_tb.hierarchy LIKE out_tb.hierarchy   N'%'
GROUP BY out_tb.ID,
         out_tb.Hierarchy,
         out_tb.Price;