Cosmo
Cosmo

Reputation: 369

Recursive COUNT Query (SQL Server)

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

Category

Question

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 :(

Upvotes: 3

Views: 5737

Answers (1)

araqnid
araqnid

Reputation: 133492

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).

Upvotes: 10

Related Questions