CODEWITHSUNDEEP
sqlpostgresqltreecountchildren
Danfi
Danfi

Reputation: 1272

postgresql count the number of children

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!

Upvotes: 4

Views: 4718

Answers (2)

Igor Volkov
Igor Volkov

Reputation: 1

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;

Upvotes: 0

András Váczi
András Váczi

Reputation: 3002

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.

Upvotes: 16

Related Questions