Aggregate query on a tree implementation as a relation (sql table)

Question

Given the following tree structure in a SQL table, and assuming the data is consistent (there are no rows with the same name, but different parents):

| name | parent | value |
|------|--------|-------|
| a    | null   |    10 |
| b    | a      |    15 |
| b    | a      |     4 |
| c    | a      |    15 |
| d    | a      |    10 |
| e    | b      |     5 |
| f    | b      |     5 |
| g    | null   |    20 |

I am looking for a query to sum up all sub-categories of a given node, like this:

| name | parent | value |
|------|--------|-------|
| a    | null   |    64 |
| b    | a      |    29 |
| c    | a      |    15 |
| d    | a      |    10 |
| e    | b      |     5 |
| f    | b      |     5 |
| g    | null   |    20 |

So, I can make only the first level of summation, and I can think of joining this to table itself on parent and and sum again... but I am looking for a solution for trees of unspecified depth. For the level 1 I have for example:

SELECT
    NAME,
    PARENT,
    SUM(VALUE) AS VALUE
FROM
    TEST
GROUP BY
    NAME,
    PARENT
ORDER BY
    NAME ASC;

Answer 1

try this CTE

WITH RECURSIVE Tree AS (
    SELECT
        name,
        parent,
        value
    FROM
        TEST

    UNION ALL

    SELECT
        t.name,
        t.parent,
        t.value + COALESCE(tr.value, 0) AS value
    FROM
        TEST t
    JOIN
        Tree tr ON t.parent = tr.name
)

SELECT
    name,
    parent,
    SUM(value) AS value
FROM
    Tree
GROUP BY
    name,
    parent
ORDER BY
    name ASC;

Answer 2

Hi Ravi,

To handle aggregation over a hierarchical structure (tree) in SQL where the depth of the tree is unspecified, you can use a recursive common table expression (CTE). This will allow you to aggregate values across all descendants of a given node, regardless of the depth of the tree.

SQL Query Explanation:
We will use a recursive CTE to navigate the hierarchy, starting from the root node (parent IS NULL) and traversing all the child nodes, summing up the value for each node and its descendants.

Here is the query that can achieve this:

Recursive Query:

WITH RecursiveSum AS (
    -- Anchor member: start from nodes that have no parent (top-level nodes)
    SELECT
        name,
        parent,
        value
    FROM
        test
    WHERE
        parent IS NULL

    UNION ALL

    -- Recursive member: join each node with its parent, summing the values
    SELECT
        child.name,
        child.parent,
        parent.value + child.value
    FROM
        test child
    INNER JOIN RecursiveSum parent ON child.parent = parent.name
)

-- Select the final summed values for each node
SELECT 
    name,
    parent,
    SUM(value) AS total_value
FROM 
    RecursiveSum
GROUP BY
    name, parent
ORDER BY
    name ASC;

Key Points:

1. Anchor Member: This is the base case for the recursion. We start by selecting the top-level nodes (where parent IS NULL).

2. Recursive Member: In this part, we join the CTE (RecursiveSum) with the original table (test) to find the child nodes for each parent. We recursively sum the values as we traverse down the tree.

3. Final Aggregation: After recursion, we sum the value field for each node, grouping by name and parent to get the total value of each node and all its descendants.

Expected Output:

The query will return the sum of values for each node, including its descendants. Based on your input data, the output will look something like this:

name

parent

total_value

a

null

64

b

a

29

c

a

15

d

a

10

e

b

5

f

b

5

g

null

20

a: Includes its own value (10) plus all its descendants (b, c, d, e, f), resulting in a total of 64.
b: Includes its own value (sum of 15 and 4) plus its descendants (e and f), resulting in 29.
g: Has no descendants, so its total value remains 20.
This approach allows for aggregation across a hierarchy of unspecified depth, and can handle multiple levels of tree depth.

Answer 3

Please try this

SELECT 
    t1.name,
    t1.parent,
    (
        SELECT SUM(value) 
        FROM MyTestTable 
        WHERE parent = t1.name OR name = t1.name
    ) AS total_value
FROM 
    MyTestTable AS t1
GROUP BY 
    t1.name, t1.parent
ORDER BY 
    t1.name,t1.parent