Counting Children with CTEs

Have you ever wanted to get a running total of all of the descendants of each tree node? This sort of thing is useful, especially if you don’t want to pull back an entire object graph just to compute the count of a child collection.``` IF OBJECT_ID(’tempdb..#categories’) IS NOT NULL DROP TABLE #categories;

IF OBJECT_ID(’tempdb..#product_categories’) IS NOT NULL DROP TABLE #product_categories;

CREATE TABLE #categories (CategoryId INT, ParentCategoryId INT); CREATE TABLE #product_categories (CategoryId INT, ProductId INT);

INSERT INTO #categories VALUES (1, NULL); INSERT INTO #categories VALUES (2, 1); INSERT INTO #categories VALUES (3, 1); INSERT INTO #categories VALUES (4, 2); INSERT INTO #categories VALUES (5, 3); INSERT INTO #categories VALUES (6, 2);

INSERT INTO #product_categories VALUES (1, 1); INSERT INTO #product_categories VALUES (2, 15); INSERT INTO #product_categories VALUES (2, 18); INSERT INTO #product_categories VALUES (3, 12); INSERT INTO #product_categories VALUES (6, 34); INSERT INTO #product_categories VALUES (4, 35); INSERT INTO #product_categories VALUES (5, 99); INSERT INTO #product_categories VALUES (3, 43); INSERT INTO #product_categories VALUES (6, 54); INSERT INTO #product_categories VALUES (3, 92); INSERT INTO #product_categories VALUES (2, 77); INSERT INTO #product_categories VALUES (5, 62); INSERT INTO #product_categories VALUES (4, 42); INSERT INTO #product_categories VALUES (1, 11);

; WITH cte(CategoryId, ParentCategoryId, c) AS (SELECT c1.CategoryId, c1.ParentCategoryId, (SELECT COUNT(*) FROM (SELECT DISTINCT ProductId FROM #product_categories AS pc WHERE pc.CategoryId = c1.CategoryId ) AS t1 ) AS c FROM #categories AS c1 UNION ALL SELECT c2.CategoryId, c2.ParentCategoryID, d.c FROM #categories c2 INNER JOIN cte d ON c2.CategoryId = d.ParentCategoryId ) SELECT cte.CategoryId, cte.ParentCategoryId, SUM(c) AS ProductCount FROM cte GROUP BY cte.CategoryId, cte.ParentCategoryId;