title: Querying a hierarchy of parent->children - I want to sum an amount, but make sure I only count rows which have the same parent once
body: <p>I have an n-level hierarchy of categories, and a set of products which can be arbitrarily mapped into categories. The same product can be in multiple categories, including multiple children of the same parent.</p>
  
  <p>What I'm trying to do is write a report that shows the total ordered amount of a product in a parent category. For instance, if the parent category is "Bikes" and the children are "Bikes - Boys" and "Bikes - Girls", I want to make sure I'm including an ordered product only once in the "Bikes" category row even if it is in both child categories.</p>
  
  <p>The query I have now is:</p>
  
  <pre><code>WITH
    hierarchy AS (
      SELECT
        prod.id as [prodId]
      , prod.Name as [prodName]
      , cat.id as [catId]
      , cat.parentId as [catParentId]
      , cat.name as [catName]
      FROM product_cat_map AS link
      INNER JOIN product AS prod
        ON prod.id = link.productId
      INNER JOIN category AS cat
        ON cat.id = link.catId
      where cat.parentid is not null
      UNION ALL
      SELECT
        child.prodId as [prodId]
      , child.prodName as [prodName]
      , parent.id as [catId]
      , parent.parentId as [catParentId]
      , parent.name as [catName]
      FROM hierarchy AS child
      INNER JOIN category AS parent
        ON parent.id = child.catParentId
      where parent.parentid is not null
    )
  select h.catName as [category],h.prodId as [product],sum(o.amount)
  from hierarchy h
  inner join ord o on h.prodId = o.productId
  group by h.catName, h.prodId
  </code></pre>
  
  <p>The hierarchy "with" clause works great. In my example, if the bike that is in both "Bikes - Boys" and "Bikes - Girls" is ordered once, I get three rows - "Bikes", "Bikes - Boys", and "Bikes - Girls".</p>
  
  <p>Using my example above, though, if the bike that is in both "Bikes - Boys" and "Bikes - Girls" costs $1 and is ordered twice, the first row of my result set is category="Bikes", product=1, total=$4.</p>
  
  <p>I feel like I'm missing something incredibly obvious, but I can't wrap my head around it. Help?</p>