Total children values based on parent

Question

I have 2 tables: table1,table2

Parent  Child  Point            Parent     Total
a       b       100               a          0(default)   (result = 1050)
b       c       200               b          0            (result = 950)
c       d       250               c          0            (result = 750)
d       e       500

The result in table2 should be sum of the children points based on parent in table1.

a---b---c---d---e

I tried many times but cant figure out.

UPDATE table2 set Total=???

Sum of points based on child.For example, the total of a in table2 is 1050 — user3835545, Jul 16 '14 at 03:24
Table2 has your result. But how are you computing the sum based on table1? In table1, a has only one child with 100 points — Nikhil Talreja, Jul 16 '14 at 03:30
The result is based on how many child that the parent has and total all.For example, a has total 4 child which are b,c,d,e but different level. — user3835545, Jul 16 '14 at 03:37

score 3 · Accepted Answer · answered Jul 16 '14 at 05:39

3

Use a recursive CTE:

WITH RECURSIVE cte AS (
   SELECT parent, child, point AS total
   FROM   tbl1

   UNION ALL
   SELECT c.parent, t.child, c.total + t.point
   FROM   cte  c
   JOIN   tbl1 t ON t.parent = c.child
   )
SELECT *
FROM   cte

answered Jul 16 '14 at 05:39

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Jul 16 '14 at 05:37

0

It hurts my brain... The below should work for you, note that it is very rough and you will want to streamline it.

DECLARE @parent NCHAR(1), @child NCHAR(1), @runningTotal INT
SET @parent = 'a' -- set starting parent here
DECLARE myCursor CURSOR FOR SELECT [Parent], [Child], [Point] FROM table1 WHERE [Parent] = @parent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @parent, @child, @runningTotal
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT * FROM table1 WHERE [Parent] = @child)
    BEGIN
        DECLARE @point INT
        SELECT @parent = [Parent], @child = [Child], @point = [Point] FROM table1 WHERE [Parent] = @child
        SET @runningTotal = @runningTotal + @point
    END
    ELSE
    BEGIN
        BREAK
    END
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT @runningTotal

answered Jul 16 '14 at 05:37

Xeon

156
8

1

That is not valid for Postgres – Jul 16 '14 at 05:39
I didn't note the question tag and only tested with SQL Server, for other database systems it will need some changes - just an idea to solve the problem. – Xeon Jul 16 '14 at 05:43
Even in SQL Server it would be an inefficient solution (as SQL Server also supports recursive queries. A cursor based approach will always be slower than a set-based approach) – Jul 16 '14 at 05:46

Total children values based on parent

2 Answers2

Linked