I've two MS SQL tables: Category, Question. Each Question is assigned to exactly one Category. One Category may have many subcategories.
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 :(
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).
