title: Getting counts of children in a relationship
body: <p>I have 2 tables, one is for <strong>Ticketcategories</strong>:</p>
  
  <pre><code>CAT_PK, CAT_FK, CAT_Name
  </code></pre>
  
  <p>Where <code>CAT_FK</code> points to another category in the table that acts as the parent category, and is null is the category has no parents.</p>
  
  <p>and <strong>tickets</strong>:</p>
  
  <pre><code>TIC_PK,CAT_FK
  </code></pre>
  
  <p>Where CAT_FK points to the category that ticket falls under. A ticket can only have one category.</p>
  
  <p>Getting a count of tickets by each category is easy, but what I need to do is get a count of tickets for a category, including all tickets that belong to children of that category.</p>
  
  <p>So if a hierchy like this exists:</p>
  
  <pre><code>Stuff
  &gt;Blue Stuff
  &gt;Red Stuff
  &gt;Yellow Stuff
    &gt;Yellow Spotted Stuff
  </code></pre>
  
  <p>Getting a count of tickets of Stuff also includes tickets under all child categories in the count.</p>
  
  <p>I have been messing around with recursive CTE's as a solution, but i'm not sure if it is capable due to the nature of counts and groups:</p>
  
  <pre><code>With
  TicketCTE (count,CAT_PK,CAT_FK_Parent, CAT_Description)
  as
  (
  Select Count(*) as count,CAT_PK,CAT_FK_Parent, CAT_Description
  from Tickets
  JOIN TicketCategories
  ON TIC_CAT_FK = CAT_PK
  GROUP BY CAT_PK,CAT_FK_Parent,CAT_Description
  
  union all
  
  Select Count(*) as count,TicketCategories.CAT_PK,TicketCategories.CAT_FK_Parent, TicketCategories.CAT_Description
  from Tickets
  join TicketCategories
  on TIC_CAT_FK = TicketCategories.CAT_PK
  join TicketCTE
  on TicketCategories.CAT_PK = TicketCTE.CAT_PK
  GROUP BY TicketCategories.CAT_PK,TicketCategories.CAT_FK_Parent,TicketCategories.CAT_Description
  )
  Select TicketCTE.*
  from TicketCTE
  left join TicketCTE MgrCTE
  on TicketCTE.CAT_FK_Parent = MgrCTE.CAT_PK
  </code></pre>
  
  <p>Not sure if this is even possible because of the error I get, </p>
  
  <blockquote>
    <p>GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression</p>
  </blockquote>