How to count detail rows on nested categories?

Let us consider that we have Categories (with PK as CategoryId) and Products (with PK as ProductId). Also, assume that every Category can relate to its parent category (using ParentCategoryId column in Categories).

How can I get Category wise product count? The parent cat开发者_开发知识库egory should include the count of all products of all of its sub-categories as well.

Any easier way to do?

sounds like what you are asking for would be a good use for with rollup

select cola, colb, SUM(colc) AS sumc
from table
group by cola, colb
with rollup

This would give a sum for colb and a rollup sum for cola. Example result below. Hope the formatting works. The null values are the rollup sums for the group.

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

Give it a go and let me know if that has worked.

--EDIT

OK i think ive got this as it is working on a small test set i am using. Ive started to see a place where i need this myself so thanks for asking the question. I will admit this is a bit messy but should work for any number of levels and will only return the sum at the highest level.

I made an assumption that there is a number field in products.

with x
as (
    select c.CategoryID, c.parentid, p.number, cast(c.CategoryID as varchar(8000)) as grp, c.CategoryID as thisid
    from Categories as c
    join Products as p on p.Categoryid = c.CategoryID
union all
    select c.CategoryID, c.parentid, p.number, cast(c.CategoryID as varchar(8000))+'.'+x.grp , x.thisid
    from Categories as c
    join Products as p on p.Categoryid = c.CategoryID
    join x on x.parentid = c.CategoryID
)
select x.CategoryID, SUM(x.number) as Amount
from x 
left join Categories a on (a.CategoryID = LEFT(x.grp, case when charindex('.',x.grp)-1 > 0 then charindex('.',x.grp)-1 else 0 end))
                    or (a.CategoryID = x.thisid)
where a.parentid = 0
group by x.CategoryID

Assuming that Products can only point to a subcategory, here's a probable solution to the problem:

SELECT
  cp.CategoryId,
  ProductCount = COUNT(*)
FROM Products p
  INNER JOIN Categories cc ON p.CategoryId = cc.CategoryId
  INNER JOIN Categories cp ON cc.ParentCategoryId = cp.CategoryId
GROUP BY cp.CategoryId

But if the above assumption is wrong and a product can reference a parent category directly as well as a subcategory, then here's how you could count the products in this case:

SELECT
  CategoryId = ISNULL(c2.CategoryId, c1.CategoryId),
  ProductCount = COUNT(*)
FROM Products p
  INNER JOIN Categories c1 ON p.CategoryId = c1.CategoryId
  LEFT JOIN  Categories c2 ON c1.ParentCategoryId = c2.CategoryId
GROUP BY ISNULL(c2.CategoryId, c1.CategoryId)

EDIT

This should work for 3 levels of hierarchy of categories (category, sub-category, sub-sub-category).

SELECT
  CategoryId = COALESCE(c3.CategoryId, c2.CategoryId, c1.CategoryId),
  ProductCount = COUNT(*)
FROM Products p
  INNER JOIN Categories c1 ON p.CategoryId = c1.CategoryId
  LEFT JOIN  Categories c2 ON c1.ParentCategoryId = c2.CategoryId
  LEFT JOIN  Categories c3 ON c2.ParentCategoryId = c3.CategoryId
GROUP BY ISNULL(c3.CategoryId, c2.CategoryId, c1.CategoryId)

COALESCE picks the first non-NULL component. If the category is a child, it picks c3.Category, which is its grand-parent, if a parent, then its parent c2.Category is chosen, otherwise it's a grand-parent (c1.CategoryId).

In the end, it selects only grand-parent categories, and shows product count for them that includes all the subcategories of all levels.