sql server - recursive calculation in sql view error GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part -

sql server - recursive calculation in sql view error GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part -

i have simple sql server table , want find cost of each recipe. each recipe has list of ingredients. recipe may have sub recipes.

simplest illustration of recipeid-1 chicken sandwich has below ingredients:

ingredientid-1 breadstuff (an ingredient) - cost $1 ingredientid-2 fried chicken (an ingredient) - cost $5 recipeid-2 garlic sauce (a sub recipe) - cost $2

since garlic sauce recipeid-2 recipe (used sub recipe above), has below ingredients:

ingredientid-3 raw garlic - cost $1.5 ingredientid-4 water - cost $0.5

finally table structure:

now want view gives me cost of chicken sandwich, select * recursiverecipeview recipeid=1 , result be: $8 (1+5+1.5+.5).

i have tried below query error "group by, having, or aggregate functions not allowed in recursive part of recursive mutual table look 'tree'"

with tree (recipeid, depth, subrecipeid, cost) ( select recipeid, 0 depth, recipeid subrecipeid, sum(cost) [cost] recipeingredients grouping recipeid union select ri.recipeid, parent.depth + 1 depth, convert(varchar(255), parent.subrecipeid) subrecipeid, parent.cost + cast(sum(ri.cost) float) [cost] recipeingredients ri inner bring together tree parent on parent.recipeid = ri.subrecipeid )select distinct recipeid, depth, subrecipeid, cost tree

please note recipes go 10 levels deep. can help me on please?

first off, you've got problems cte. recurse infinitely, not good. flipped bring together between tree , parent objects.

secondly, has been pointed out, need pull aggregation out of cte. allow cte select details, , sum in final query. 1 grab here you'll need specify particular recipeid in top level of cte, have "top" level.

sqlfiddle (i added 3rd level , test it).

with tree (recipeid, depth, subrecipeid, cost) ( select recipeid, 0 depth, subrecipeid, cost recipeingredients recipeid = 1 union select parent.recipeid, parent.depth + 1 depth, ri.subrecipeid subrecipeid, ri.cost cost recipeingredients ri inner bring together tree parent on parent.subrecipeid = ri.recipeid ) select * tree --select sum(cost) tree

sql-server recursion