Running Subtotal on Hierarchical Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running Subtotal on Hierarchical Table

Author

Topic

FitzOs31
Starting Member

2 Posts

Posted - 2010-12-17 : 16:55:27

Hi, first time posting here. I'm a little confused about Common Table Expressions and summing deep-level sub categories in a hierarchical table.

Here's a temp table that reflects what I'm working with, where ItemCount is the count of items in that category:

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')

Where the UID is the categoryID and parentid is the hierarchy parent. I was able to retrieve the 'level' of the hierarchy using this CTE query:

;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

Is it possible to retrieve a running subtotal of the ItemCount based off the category UID? For example Category 7 would have a total of 2 Items. The query will need to drill into many sub categories and run the sub total up the hierarchy. In the best scenario I would be able to pass a level variable to the CTE and only select the count of products within it's parent category, something like:

DECLARE @CurrentLevel int;
SET @CurrentLevel = 0;

SELECT * FROM HCTE WHERE Level = @CurrentLevel
result:

CategoryID | ParentID | Name | ItemCount | Level
3 | 1 | Cars | 26 | 0

Any ideas?

thanks

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-17 : 23:37:21

...having a hard time following what exactly you are trying to do.
your data shows UID 7 has an ItemCount = 9. How do you figure it should
have a total of 2 items? You should be able to do what you want without looping @CurrentLevel.
..please try to elaborate on your objective.
thanks,
..bob

FitzOs31
Starting Member

2 Posts

Posted - 2010-12-20 : 09:41:30

Hi Bob, typo on my end - must've goofed something, here's the insert with the correct data:

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')

Car Make 1 (UID 7) would have a Level of '1' and a '0' Item Count' However, it's child (UID 11- Car Model D) would have an Item Count of '4'. I would like to sum that amount up to the parent, so that UID 7 would have a total item count of 4. Basically I need to sum the child item count up to the parent for each given level.

If I go level to '0', UID 3 - Cars has Car Make 1 and Car Make 2 as children (both 0 count) and those have Car Model D and Car Model X respectfully '4' and '2' Item count. The total Item count for UID 3 Cars would be 20+ 0 + 0 + 4 + 2 = 26.

Thanks

Mike

matty
Posting Yak Master

161 Posts

Posted - 2010-12-22 : 05:07:04

[code]
;with CTE(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 CTE ch
on ch.CategoryID = c.ParentID
)

select * from
(
select CategoryID, ParentID, Name,SUM(ItemCount) OVER() AS Total,Level from CTE
)c
where c.Level = 0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources