CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Tobia
Tobia

Reputation: 9506

TSQL GROUP BY in recursive CTE

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.

Upvotes: 8

Views: 19629

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

;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

Upvotes: 5

Neil
Neil

Reputation: 919

I recommend using a variable table instead. Declare the table and then insert those records into it. You would need to figure out a way to loop through it on the second insert into command. I got this to get you started:

DECLARE @staging TABLE
(
    id                  INT
    ,dailyconsumption   FLOAT
    ,cutoff             FLOAT
)

INSERT INTO @staging
    SELECT
      id,
      dailyconsumption,
      stock/dailyconsumption as cutoff
    FROM 
      items
    WHERE 
      father IS NULL

INSERT INTO @staging
  SELECT
      i.id,
      SUM(father.dailyconsumption*i.num),
      MAX(stock)/SUM(father.dailyconsumption*i.num)
    FROM 
      @staging father

        JOIN items i 
        ON father.id=i.father
    group by 
      i.id

SELECT 
  id
 ,MIN(cutoff)
FROM 
  @staging
GROUP BY 
  id

Upvotes: 0

Related Questions