Avoid subqueries when grouping over a part of a string

Question

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)

ID	Hierarchy	Price
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:

ID	Hierarchy	Price	Total_Price
1	001	10	50
2	001.001	20	20
3	001.002	5	14
4	001.002.001	3	3
5	001.002.002	2	2
6	001.002.003	4	4
7	001.003	6	6

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?

score 3 · Accepted Answer · answered Dec 14 '22 at 05:16

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;

Your answer solves the problem :) I was hoping to avoid a join since the table has many columns and some of them are dynamic. I don't want to write their names if possible. With a group by I would need to do that. But I think I can just extract the relevant columns to a temp table, do the transformation there and then join everything back to the big table. Thank you for paving the way ;) — Merlin Nestler, Dec 14 '22 at 05:31

Avoid subqueries when grouping over a part of a string

1 Answers1