TSQL GROUP BY in recursive CTE

Question

Is there a workaround to use GROUP BY inside a looping CTE or there is a workaround?

I need to group resultset of a CTE table and use it in another loop with the same CTE, but i get following error:

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte'.

Here's the query:

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

SQL-Fiddle (with sample data)

---

EDIT... this is the logical problem

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

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

http://sqlfiddle.com/#!3/f4f2a/95

With this valid 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.

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: stock/dailyconsumption. For sub-items it is a little more complicated: subitem.stock/subitem.dailyconsumption + MIN(father.cutoff) where MIN(father.cutoff) is the minimun cutoff from all fathers of this subitem. This is because i need another group by.

May I need another CTE to loop in the same father-son relationship?

Thank you for your attention and sorry for my English.

Answer 1

;WITH cte AS
 (
  SELECT id, father, 
         dailyconsumption,
         (stock / dailyconsumption) AS cutoff,
         0 AS [Level] 
  FROM items
  WHERE father IS NULL
  UNION ALL
  SELECT i.id, i.father, 
         c.dailyconsumption * i.num,
         i.stock / (c.dailyconsumption * i.num),
         [Level] + 1
  FROM cte c JOIN items i ON c.id = i.father
  )
  SELECT c.id, c.dailyconsumption, c.cutoff AS subItemsCutoff, 
         MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]) AS fatherCutoff,
         (c.cutoff  + ISNULL(MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]), 0)) AS Cutoff
  FROM cte c LEFT JOIN cte ct ON c.father = ct.id

Demo on SQLFiddle