T-SQL - Querying Hierarchical Table with Running Subtotal (each row has an item count)

Question

I'm trying to create a dynamic navigation based off of a hierarchical table which follows this design:

CREATE TABLE #CPTree (UID int, ParentID int, Name nvarchar(150), ItemCount int)

INSERT INTO #CPTree VALUES ('1' , '0', 'Vehicles', '0')

INSERT INTO #CPTree VALUES ('2' , '1', 'Bikes', '10')

INSERT INTO #CPTree VALUES ('3' , '1', 'Cars', '20')

INSERT INTO #CPTree Values ('5' , '2', 'Bike Make 1', '0')

INSERT INTO #CPTree Values ('6' , '2', 'Bike Make 2', '5')

INSERT INTO #CPTree Values ('7' , '3', 'Car Make 1', '0')

INSERT INTO #CPTree Values ('8' , '3', 'Car Make 2', '0')

INSERT INTO #CPTree Values ('9' , '5', 'Bike Model A', '7')

INSERT INTO #CPTree Values ('10' , '5', 'Bike Model B', '1')

INSERT INTO #CPTree Values ('11' , '7', 'Car Model D', '4')

INSERT INTO #CPTree Values ('12' , '8', 'Car Model X', '2')

--Retrieve level of category/child categories with CTE

;with HCTE(CategoryID, ParentID, Name, itemcount, Level) as ( select UID, ParentID, Name, itemcount, 0 as Level from #CPTree c where c.UID = 3 -- Cars Category

union all select c.UID, c.ParentID, c.Name, c.itemcount, ch.Level + 1 from #CPTree c inner join HCTE ch on ch.CategoryID = c.ParentID )

SELECT * FROM HCTE

DROP TABLE #CPTree

--end

I would like to retrieve the total item count for each category, for example the Top level category 'Cars' (item count 20) has 2 children (0 item count for both), and each of those have children (2 and 4 item count respectfully). The total item count for Cars would be 26. Likewise, the total item count for Car Make 1 (CategoryID 7) would be 4 (sum from it's child - CategoryID 11). The result would return this:

CategoryID | ParentID | Name | ItemCount | Level | TotalItemCount

3 | 1 | Cars | 20 | 0 | 26

7 | 3 | Car Make 1 | 0 | 1 | 4

8 | 3 | Car Make 2 | 0 | 1 | 2

12 | 8 | Car Model X | 2 | 2 | 2

11 | 7 | Car Model D 4 | 2 | 4

This would allow me to see if subcategories had any items within their subcategories, but not necessarily if they had any items themselves. My live table has many levels, so the query would have to continue to drill through the categories until it hit the last level. I can provide a deeper example if necessary.

Any help is appreciated! Thanks

djo djo · Accepted Answer · 2010-12-21T18:49:52

The idea used is to add a trail of ancestors (as a string) for each node in the tree, then to sum up all rows that have that node in it.

   ;with HCTE(CategoryID, ParentID, Name, itemcount, Level, trail)
    as
    (
     select UID, ParentID, Name, itemcount, 0 as Level, 
                                                  '/' + cast(UID as varchar(max))+ '/' as trail 
     from #CPTree c
     where c.UID = 3 -- Cars Category


union all

select c.UID, c.ParentID, c.Name, c.itemcount, ch.Level + 1, 
                                               trail + CAST(UID as varchar(max)) + '/'
from #CPTree c 
inner join HCTE ch on ch.CategoryID = c.ParentID
)
select CategoryID, ParentID, Name, ItemCount,
   (select SUM(ItemCount)
    from HCTE h2
    where charindex('/' + CAST(h1.CategoryID as varchar(max)) + '/', h2.trail, 1) <> 0
   )
 from hcte h1

T-SQL - Querying Hierarchical Table with Running Subtotal (each row has an item count)

3 Answers