tsql - How to count detail rows on nested categories? -

let consider have categories (with pk categoryid) , products (with pk productid). also, assume every category can relate parent category (using parentcategoryid column in categories).

how can category wise product count? parent category should include count of products of of sub-categories well.

any easier way do?

sounds asking use rollup

select cola, colb, sum(colc) sumc table group cola, colb rollup 

this give sum colb , rollup sum cola. example result below. hope formatting works. null values rollup sums group.

cola    colb    sumc 1             1 1       b       4 1       null    5 2       c       2 2       d       3 2       null    5 null    null    10 

give go , let me know if has worked.

--edit

ok think ive got working on small test set using. ive started see place need myself asking question. admit bit messy should work number of levels , return sum @ highest level.

i made assumption there number field in products.

with x (     select c.categoryid, c.parentid, p.number, cast(c.categoryid varchar(8000)) grp, c.categoryid thisid     categories c     join products p on p.categoryid = c.categoryid union     select c.categoryid, c.parentid, p.number, cast(c.categoryid varchar(8000))+'.'+x.grp , x.thisid     categories c     join products p on p.categoryid = c.categoryid     join x on x.parentid = c.categoryid ) select x.categoryid, sum(x.number) amount x  left join categories on (a.categoryid = left(x.grp, case when charindex('.',x.grp)-1 > 0 charindex('.',x.grp)-1 else 0 end))                     or (a.categoryid = x.thisid) a.parentid = 0 group x.categoryid