postgresql count the number of children

Question

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!

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 http://www.rampant-books.com/book_0601_sql_coding_styles.htm or other references. — J.Spiral, Sep 06 '12 at 06:18
@hims056: What's unclear? Follow the `parent_id` links and recursively sum the `count` values. — mu is too short, Sep 06 '12 at 06:18
@hims056 the first table is what I have, and the second is what I want to get to create a view — Danfi, Sep 06 '12 at 06:26
possible duplicate of [Is it possible to make a recursive SQL query?](http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query) — splash, Sep 06 '12 at 06:53

score 16 · Accepted Answer · answered Sep 06 '12 at 07:47

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.

Thank you for your answer. I made a mistake, I really wanted to get the total value but not the count. Sorry. — Danfi, Sep 07 '12 at 04:08
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. — lared, Feb 19 '15 at 18:55

Igor Volkov · Answer 2 · 2023-05-17T06:15:00.397

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;

edited May 17 '23 at 06:15

answered May 16 '23 at 13:45

Igor Volkov

1
1

1

Please provide explanation – Arda Kazancı May 17 '23 at 11:29
The code above can help to count children number for the root element using recursion and CTE. – Igor Volkov May 22 '23 at 07:21

postgresql count the number of children

2 Answers2