4

The tree is unlimited depth. example:

+----+-------+--------------------+
| id | name  | parent_id |  value |
+----+-------+--------------------+
|  1 | test1 |           |    0   |
|  2 | test2 |     1     |    0   |
|  3 | test3 |     2     |    5   |
|  4 | test4 |     1     |    0   |
|  5 | test5 |     4     |    5   |
|  6 | test6 |     4     |    0   |
|  7 | test7 |     6     |    10  |
+----+-------+--------------------+

I want to get the total value of one's children. just like this:

+----+-------+--------------------+
| id | name  | parent_id |  value |
+----+-------+--------------------+
|  1 | test1 |           |    20  |  =  test2.value + test4.value
|  2 | test2 |     1     |    5   |  =  test3.value
|  3 | test3 |     2     |    5   |  
|  4 | test4 |     1     |    15  |  =  test5.value + test6.value
|  5 | test5 |     4     |    5   |
|  6 | test6 |     4     |    10  |  =  test7.value
|  7 | test7 |     6     |    10  |
+----+-------+--------------------+

any suggestion ? Thanks!

Improve this question
4
  • Without knowing the rest of your needs, I can't say anything definitive about what the right pattern would be but your implementation is a pretty naive way to store trees in a database. Some design patterns have been invented that make it much easier to do all sorts of clever things, including the question you ask. For example, check out the materialized path design pattern. See rampant-books.com/book_0601_sql_coding_styles.htm or other references. Commented Sep 6, 2012 at 6:18
  • @hims056: What's unclear? Follow the parent_id links and recursively sum the count values. Commented Sep 6, 2012 at 6:18
  • @hims056 the first table is what I have, and the second is what I want to get to create a view Commented Sep 6, 2012 at 6:26
  • possible duplicate of Is it possible to make a recursive SQL query? Commented Sep 6, 2012 at 6:53

2 Answers 2

Reset to default
16

Here is a recursive query which hopefully solves your problem:

WITH RECURSIVE tree (id, parent_id, cnt) AS (
    -- start from bottom-level entries
    SELECT id, parent_id, 0::bigint AS cnt
    FROM tbl t
    WHERE NOT EXISTS (
        SELECT id
        FROM tbl
        WHERE parent_id = t.id
    )

    UNION ALL

    -- join the next level, add the number of children to that of already counted ones
    SELECT t.id, t.parent_id, tree.cnt + (
            SELECT count(id) 
            FROM tbl 
            WHERE parent_id = t.id
        )
    FROM tbl t JOIN tree ON t.id = tree.parent_id
)
SELECT tree.id, max(tree.cnt) AS number_of_children
FROM tree 
-- use the JOIN if you need additional columns from tbl
-- JOIN tbl ON tree.id = tbl.id 
-- use the WHERE clause if you want to see root nodes only
-- WHERE parent_id IS NULL
GROUP BY tree.id
ORDER BY tree.id
;

I made an SQLFiddle, too.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your answer. I made a mistake, I really wanted to get the total value but not the count. Sorry.
I'm afraid your implementation is incorrect. Please consider a tree with a root which has three children, the two first have two children each and the last one has no children. According to your query the root has 5 children (descendants), while in reality it has 7.
0 
WITH RECURSIVE tree (id, parent_id) AS (
    SELECT id, parent_id
    FROM tbl
    UNION ALL
SELECT t.id, t.parent_id
FROM tbl t JOIN tree ON t.id = tree.parent_id
)
SELECT id, count(*) FROM tree
GROUP BY id
ORDER BY id;
Improve this answer

2 Comments

Please provide explanation
The code above can help to count children number for the root element using recursion and CTE.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.