Aggregating Values from a Hierarchy

In a session about the SQL Server 2008 datatype HierarchyID, the question came up whether you can easily ask question such as: "If I store things (and sup-things and so on) with a number in a hierachy, can I sum up the numbers of a thing, including the subthings?"
The answer is, yes, you can, and here is how it goes. The following code shows a table which hierarchically stores websites and their clickrate.

-- create hierarchical table
create table websiteCalls
(
path hierarchyid,
filename nvarchar(100),
clickCount int
)

-- insert root node "index"
insert into websiteCalls values (hierarchyid::GetRoot(), 'index.html', 1000)
go

-- insert a level 1 entry "products"
declare @index hierarchyid
select @index = path from websiteCalls where filename='index.html'
insert into websiteCalls values (@index.GetDescendant(null, null), 'products.html', 200)

-- insert two more level 1 entries "customers" and "impressum"
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
insert into websiteCalls values (@index.GetDescendant(@products, null), 'customers.html', 100)
insert into websiteCalls values (@index.GetDescendant(null, @products), 'impressum.html', 10)

-- insert a "product1" on level 2
insert into websiteCalls values (@products.GetDescendant(null, null), 'product1.html', 300)
declare @product1 hierarchyid
select @product1 = path from websiteCalls where filename='product1.html'

-- insert two more level 2 entries
insert into websiteCalls values (@products.GetDescendant(@product1, null), 'product2.html', 50)
insert into websiteCalls values (@products.GetDescendant(null, @product1), 'product0.html', 100)
go

-- get all websites and their clickrates
select path.ToString(), filename, clickCount from websiteCalls

-- get the sum of clickrate number of all product-pages:
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
select SUM(clickcount) from websiteCalls where path.IsDescendantOf(@products) = 1 and path<>@products