title: recursive calculation in sql view error GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part
body: <p>I have a very simple SQL Server table and I want to find cost of each recipe. Each Recipe has a list of ingredients. A recipe may also have a sub recipes.</p>
  
  <p>Simplest Example of RecipeId-1 Chicken sandwich which has below ingredients:</p>
  
  <ol>
  <li>IngredientId-1 Bread (an ingredient) - Cost $1</li>
  <li>IngredientId-2 Fried Chicken (an ingredient) - Cost $5</li>
  <li>RecipeId-2 Garlic Sauce (a sub recipe) - Cost $2</li>
  </ol>
  
  <p>Since garlic sauce RecipeId-2 is also a recipe (used as sub recipe above), it has below ingredients:</p>
  
  <ol>
  <li>IngredientId-3 Raw Garlic - Cost $1.5</li>
  <li>IngredientId-4 Water - Cost $0.5</li>
  </ol>
  
  <p>Finally my table structure:</p>
  
  <p><img src="https://i.stack.imgur.com/58XgP.png" alt="enter image description here"></p>
  
  <p>Now I want a view that gives me the cost of Chicken Sandwich, Something like 
  Select * From RecursiveRecipeView Where RecipeId=1 and result will be: $8 (1+5+1.5+.5). </p>
  
  <p>I have tried below query but I get error "GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Tree'"</p>
  
  <pre><code>WITH Tree (RecipeId, Depth, SubRecipeId, Cost) AS (
  SELECT RecipeId, 0 AS Depth, RecipeId AS SubRecipeId, SUM(Cost) AS [Cost] FROM RecipeIngredients
  GROUP BY RecipeId
  UNION ALL
  SELECT RI.RecipeId, Parent.Depth + 1 AS Depth,
  CONVERT(varchar(255), Parent.SubRecipeId) AS SubRecipeId, Parent.Cost + cast(sum(RI.cost) as float) AS [Cost] 
  FROM RecipeIngredients RI
  INNER JOIN Tree as Parent ON Parent.RecipeId = RI.SubRecipeId )SELECT distinct RecipeId, Depth, SubrecipeId, Cost FROM Tree
  </code></pre>
  
  <p>Please note that some recipes go even 10 levels deep. Can somebody help me on this please?</p>