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;

Tahir Ansari · Answer

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;

Aman Gupta · Answer

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: Anchor Member : This is the base case for the recursion. We start by selecting the top-level nodes (where parent IS NULL ). 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. 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.

Mohammad Hussain · Answer

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.namet1.parent