How can I recursively calculate a value

Question

I have this table.

Bundles

id | parent_id | quantity
 1 | 0         | 1
 2 | 1         | 4
 3 | 2         | 5

I want to get the total quantity of a bundle with id 3, which is 1 * 4 * 5 = 20 items

Can this be done with a single query?

Answer 1

Here's a solution using CTE:

Setup:

CREATE TABLE Table1
    (id int, parent_id int, quantity int)
;

INSERT INTO Table1
    (id, parent_id, quantity)
VALUES
    (1, 0, 1),
    (2, 1, 4),
    (3, 2, 5),
    (4, 0, 7),
    (5, 4, 10)
;

CTE to return total of id=3 and it's parent items:

;WITH myCTE AS
(
    SELECT id, parent_id, quantity
    FROM Table1
    WHERE id = 3
    UNION ALL
    SELECT T.id, T.parent_id, T.quantity
    FROM Table1 T
       JOIN myCTE C ON T.id = C.parent_id
)
SELECT EXP(sum(log(quantity))) 
FROM myCTE

Demo SQL Fiddle

Multiplication method for values in a column, SELECT EXP(sum(log(quantity))), taken from here.