title: TSQL GROUP BY in recursive CTE
body: <p>Is there a workaround to use GROUP BY inside a looping CTE or there is a workaround?</p>
  
  <p>I need to group resultset of a CTE table and use it in another loop with the same CTE, but i get following error:</p>
  
  <blockquote>
    <p>GROUP BY, HAVING, or aggregate functions are not allowed in the
    recursive part of a recursive common table expression 'cte'.</p>
  </blockquote>
  
  <p>Here's the query:</p>
  
  <pre><code>WITH cte
      AS
      (
          SELECT
            id,
            dailyconsumption,
            stock/dailyconsumption as cutoff
          FROM items
          WHERE father IS NULL
  
  
          UNION ALL
  
          SELECT
            i.id,
            SUM(father.dailyconsumption*i.num),
            MAX(stock)/SUM(father.dailyconsumption*i.num)
          FROM cte father
          JOIN items i ON father.id=i.father
          group by i.id
      )
  
  SELECT id, MIN(cutoff)
  FROM cte
  GROUP BY id
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!3/f4f2a/11" rel="noreferrer"><strong>SQL-Fiddle</strong></a> (with sample data)</p>
  
  <hr>
  
  <p><strong>EDIT... this is the logical problem</strong></p>
  
  <p>I have a set of end-user items (father=NULL) and other sub-items made by a number of other items (field father and field num populated).
  I got the dailyconsumption just for the end-user items (I start my cte with "WHERE father IS NULL"), and sub-items's dailyconsumption are calculate by SUM(father.dailyconsumption *item.num).</p>
  
  <pre><code>WITH cte AS(
      SELECT
        id,
        dailyconsumption,
        stock/dailyconsumption as cutoff
      FROM items
      WHERE father IS NULL
  
  
      UNION ALL
  
      SELECT
        i.id,
        father.dailyconsumption*i.num
        0
      FROM cte father
      JOIN items i ON father.id=i.father
  )
  
  SELECT id, SUM(dailyconsumption)
  FROM cte
  GROUP BY id
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!3/f4f2a/95" rel="noreferrer">http://sqlfiddle.com/#!3/f4f2a/95</a></p>
  
  <p>With this <strong>valid</strong> query I'm going to have all dailyconsumption populated for all items (end-user and sub-items). Please mind that father-son relationship can be more than 1 level deep.</p>
  
  <p>Now i need to calculate the cutoff (for how many days my stock is enought).
  For end-use it is very easy and already calculated in first CTE: <strong>stock/dailyconsumption</strong>.
  For sub-items it is a little more complicated: 
  <strong>subitem.stock/subitem.dailyconsumption + MIN(father.cutoff)</strong>
  where MIN(father.cutoff) is the minimun cutoff from all fathers of this subitem.
  This is because i need another group by.</p>
  
  <p>May I need another CTE to loop in the same father-son relationship?</p>
  
  <p>Thank you for your attention and sorry for my English.</p>