Jeremiah Peschka

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;

So, how does mess work? Carefully.

The innermost select, t1, builds a virtual table that is nothing more than the product count under a single Category. There is a DISTINCT on here just to make sure that there are no duplicates from the #product_categories table. Theoretically this should be a group by, but this was a super fast hotfix that needed to go into production ASAP. Mistakes happen.

Moving outwards we encounter a SELECT COUNT on t1. This is where we get the actual count of products under the current category. Pretty standard.

What’s interesting, to me at least, is that in the recursive portion of the CTE, we’re working our way UP the hierarchy, from the bottom. This makes sure that when we do a sum of c, grouped by category and parent category, we’ll have the proper count in our CTE results.

What’s the advantage of this approach? You don’t have nasty deeply nested sub-selects. You can use this to populate a ChildCount column in your table as a result of a trigger. This is also, usually, fast enough to run live or else to use as a materialized view.