Clever Geek Handbook

SUM in a recursive procedure

I have a problem with a recursive procedure. Here is a script I made that works well (apart from the amount I will explain later):

;WITH RESULT (MOTHER, CHILD, QUANTITY) as
(
    select Mother, Child, CONVERT(Numeric(10,0), Quantity) as Quantity 
    from bilangammestest 

    union all 

    select M.mother, R.Child, CONVERT(Numeric(10,0), M.quantity * R.Quantity) as Quantity 
    from Result R 
    INNER JOIN bilangammestest M ON M.Child = R.Mother
)

select * from result
where mother not in (select child from bilangammestest)

Here is the data I have in my table Bilangammestest

:

Z A 1    
Z Y 1    
A B 2    
Y B 2    
B C 3

Here's the result:

Z A 1    
Z Y 1    
Z C 6    
Z C 6    
Z B 2    
Z B 2

Here is the end result I want:

Z A 1    
Z Y 1    
Z C 12    
Z B 4

I tried to do sum

but I could not do it right

+3


source to share


2 answers


The last request should be:



select MOTHER, CHILD, sum(quantity) quantity
from result
where mother not in (select child from bilangammestest )
group by MOTHER, CHILD
0


source


Use group by 



SELECT firstcolname, secondcolname, sum(thircolname) GROUP BY firstcolname, secondcolname
+3


source






More articles:


All Articles