title: Why does adding one line to this SQL Server CTE expression change result
body: <p>I am trying to create a CTE (common table expression) to create a manifest BOM (Bill of Materials). I.e. a query descends through an entire BOM and summarise the quantity of each unique part.</p>
  
  <p>I also need to return some data about the BOM items.</p>
  
  <p>I am trying to add a calculated field to show if the BOM item is an assembly or not, i.e. If it also has bom entries.  What am I doing wrong?</p>
  
  <p>The database is Aras Innovator, which uses SQL Server.</p>
  
  <p>The records structure is </p>
  
  <pre><code>&lt;Part_Id&gt; - &lt;source_id : Bom Entry : related_id&gt; - &lt;Part_Id&gt;
  </code></pre>
  
  <p>So there are essentially two tables. Though the query also returns data from other related tables.</p>
  
  <p>The first query works, but when I try and add the relationship showing the part is an assembly, the sum becomes wrongs and the wrong number of items is returned.</p>
  
  <pre><code>&lt;!-- language: lang-sql --&gt;
  WITH RecursiveBOM(source_id, related_id, quantity) AS
  (
    SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
      FROM innovator.PART_BOM AS pbom
      WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  
    UNION ALL
  
    SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
      FROM innovator.PART_BOM AS pbom
      INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
  )
  SELECT
    rp.MAKE_BUY as MAKE_BUY,
    rp.ITEM_NUMBER AS PN,
    rp.NAME AS NAME,
    sum(RecursiveBOM.quantity) as Total,
    mp.ITEM_NUMBER as MPN,
    man.NAME as MANUFACTURER,
    vp.CATALOG_NUMBER as SPN,
    ven.NAME as SUPPLIER,      
    CAST(CASE WHEN count(cp.RELATED_ID)&gt;0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
  
  FROM RecursiveBOM
  
  INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
  INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
  
  --Manufacturer Part
  LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
  LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
  LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
  
  --Supplier
  LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
  LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
  
  --Has drawings
  LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
  
  --Don't decend into PCBA
  WHERE sp.classification NOT LIKE '%PCBA%'
  
  --Collect the results 
  GROUP BY
    rp.ITEM_NUMBER,
    mp.ITEM_NUMBER,
    rp.NAME,
    man.NAME,
    vp.CATALOG_NUMBER,
    ven.NAME,
    cp.RELATED_ID,
    rp.MAKE_BUY
  </code></pre>
  
  <p>I've added only two lines, which are</p>
  
  <pre><code>...
  CAST(CASE WHEN count(bom.RELATED_ID)&gt;0 THEN 'Yes' ELSE 'No' END as TEXT) AS IS_ASSY`
  ...
  --Is Assembly
  LEFT JOIN innovator.PART_BOM bom ON bom.SOURCE_ID = rp.ID
  ...
  </code></pre>
  
  <p>This produces the wrong sums:</p>
  
  <pre><code>&lt;!-- language: lang-sql --&gt;
  WITH RecursiveBOM(source_id, related_id, quantity) AS
  (
    SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
      FROM innovator.PART_BOM AS pbom
      WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  
    UNION ALL
  
    SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
      FROM innovator.PART_BOM AS pbom
      INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
  )
  SELECT
    rp.MAKE_BUY as MAKE_BUY,
    rp.ITEM_NUMBER AS PN,
    rp.NAME AS NAME,
    sum(RecursiveBOM.quantity) as Total,
    mp.ITEM_NUMBER as MPN,
    man.NAME as MANUFACTURER,
    vp.CATALOG_NUMBER as SPN,
    ven.NAME as SUPPLIER,      
    CAST(CASE WHEN count(cp.RELATED_ID)&gt;0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
    CAST(CASE WHEN count(bom.RELATED_ID)&gt;0 THEN 'Yes' ELSE 'No' END as TEXT) AS IS_ASSY
    FROM RecursiveBOM
  
  INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
  INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
  
  --Manufacturer Part
  LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
  LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
  LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
  
  --Supplier
  LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
  LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
  
  --Has drawings
  LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
  
  --Is Assembly
  LEFT JOIN innovator.PART_BOM bom ON bom.SOURCE_ID = rp.ID
  
  --Don't decend into PCBA
  WHERE sp.classification NOT LIKE '%PCBA%'
  
  --Collect the results 
  GROUP BY
    rp.ITEM_NUMBER,
    mp.ITEM_NUMBER,
    rp.NAME,
    man.NAME,
    vp.CATALOG_NUMBER,
    ven.NAME,
    cp.RELATED_ID,
    rp.MAKE_BUY
  </code></pre>