Sum on a Recursive Self-Join

Question

I found an extremely helpful article located at: Simplest way to do a recursive self-join in SQL Server?

Suppose in this example that there was another column called "Quantity" that stores integers that looks like so:

PersonID | Initials | ParentID  |  Quantity
1          CJ         NULL            1
2          EB         1               2
3          MB         1               1
4          SW         2               1
5          YT         NULL            1
6          IS         5               1

If I requested CJ's hierarchy, it would be

PersonID | Initials | ParentID  |  Quantity |  HasSubordinate
1          CJ         NULL            2            1
2          EB         1               1            1
3          MB         1               1            1
4          SW         2               1            0

The column HasSubordinate specifies the last individual in the hierarchy. I would like to display the last person in the hierarchy with the quantities of each preceding row multiplied together. In this case, the quantity would be 2 (2 x 1 x 1 x 1 = 2).

PersonID | Initials | ParentID  |  Quantity |  HasSubordinate
4          SW         2               2            0

My Code:

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   PersonID = 1
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
WHERE HasSubordinate = 0

Any help is greatly appreciated!!

Hart CO · Accepted Answer · 2015-02-24T02:07:17.870

1

You can add a new field to your recursive cte and multiply as you iterate through:

WITH    q AS 
        (
        SELECT  *,Quantity AS Tot_Qty
        FROM    mytable
        WHERE   PersonID = 1
        UNION  ALL
        SELECT  m.*,m.Quantity * q.Tot_Qty AS Tot_Qty
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
WHERE HasSubordinate = 0

Note: This will get you 2 x 1 x 1 not 2 x 1 x 1 x 1 because you're using ParentID.

edited Feb 24 '15 at 02:07

answered Feb 23 '15 at 18:36

Hart CO

34,064
6
48
63

Awesome! That's exactly what I was looking for, but for some reason could not come up with it. Just a note, there was an initial error complaining about incompatible types so I had to hardcode Quantity as a decimal. Thank you for the help!! – user2572833 Feb 23 '15 at 18:56
I don't think this is correct answer. If you change quantities from 1 to 2 for example, you should get 2*2*2*2 = 16, but this query will return 8 – Giorgi Nakeuri Feb 23 '15 at 19:25
@GiorgiNakeuri Agreed, hence the note at the bottom of my answer, I was thinking about alternatives but left it alone after it was accepted. – Hart CO Feb 23 '15 at 20:05
Ah, if only a running total of multiplicands was so easy! This doesn't work because all the rows at the next tier down from their root all see their root, not each other. The output of the recursive cte makes it look like you can just multiply them all together, but not so. – TommCatt Feb 24 '15 at 01:16
@TommCatt I did realize I wasn't referencing the recursive product, so that's been updated. This works so long as you have 1 child per parent, for example, in the sample data, `PersonID = 3` isn't included in the total for `PersonID = 4` because they aren't in the parent/child line for that record. That's what I intended to note at the bottom of my answer, but could have done a better job of explaining. – Hart CO Feb 24 '15 at 02:09

score 0 · Answer 2 · answered Feb 24 '15 at 01:37

Every now and then, someone complains there is no MULT aggregate function. Maybe some day there will be but until then, we have to cheat. The following is based on the fact that LOG( a * b * c ) = LOG( a ) + LOG( b ) + LOG( c ). Unfortunately, it requires one extra level of CTE (not recursive though) but it eventually arrives at an answer.

with
List( PersonID, Initials, ParentID, Qty )as(
    select  1, 'CJ', null,  1 union all
    select  2, 'EB', 1,     2 union all
    select  3, 'MB', 1,     3 union all
    select  4, 'SW', 2,     4 union all
    select  5, 'YT', null,  2 union all
    select  6, 'IS', 5,     5
),
CTE( PersonID, Initials, ParentID, Qty, Root )as(
    select  l.PersonID, l.Initials, l.ParentID, l.Qty, l.PersonID
    from    List    l
    where   l.ParentID is null
        --and l.Initials = 'CJ'
    union all
    select  l.PersonID, l.Initials, l.ParentID, l.Qty, c.Root
    from    CTE     c
    join    List    l
        on  l.ParentID = c.PersonID
),
Logs( PersonID, Initials, ParentID, Qty, Root, SumLog )as(
    select *, sum( log( Qty )) over( partition by Root)
    from CTE
)
select  *, exp( SumLog ) as Mult
from    Logs
order by PersonID;

Which generates this result:

PersonID Initials ParentID Qty Root SumLog           Mult
-------- -------- -------- --- ---- ---------------- ----
1        CJ       NULL       1    1 3.17805383034795   24
2        EB       1          2    1 3.17805383034795   24
3        MB       1          3    1 3.17805383034795   24
4        SW       2          4    1 3.17805383034795   24
5        YT       NULL       2    5 2.30258509299405   10
6        IS       5          5    5 2.30258509299405   10

This satisfies the requirements as stated, pulling the last row will have the total of all QTYs multiplied together -- they all have that value. Maybe someone clever can generate a running total. I'll leave that as an exercise (meaning I'm too lazy to try it myself).

Sum on a Recursive Self-Join

2 Answers2