Recursive COUNT Query (SQL Server)

Question

I've two MS SQL tables: Category, Question. Each Question is assigned to exactly one Category. One Category may have many subcategories.

Category

• Id : bigint (PK)
• Name : nvarchar(255)
• AcceptQuestions : bit
• IdParent : bigint (FK)

Question

• Id : bigint (PK)
• Title : nvarchar(255) ...
• IdCategory : bigint (FK)

How do I recursively count all Questions for a given Category (including questions in subcategories). I've tried it already based on several tutorials but still can't figure it out :(

Answer 1

with /* recursive */ category_tree as (
  select category.id as top_category_id, category.id as category_id
  from category
union all
  select top_category_id, category.id
  from category_tree
       join category on category.idparent = category_tree.category_id
)
select category_tree.top_category_id as category, count(*) as question_count
from category_tree
     join question on question.idcategory = category_tree.category_id
group by category_tree.top_category_id

The CTE builds a list of which subcategories are under each category- essentially, it recurses through the tree and produces a flattened view of (top category, descendant category).

The initial term (before the union all) selects each category, and indicates that it contains itself- the recursive term then includes all the subcategories for categories found so far, and stops (produces no results) automatically when all the category_id columns in the previous iteration were leaf categories.

Based on that, we simply join this flattened view back onto question to produce a set of (top category, question) rows, and aggregate based on (top category).