Ravi Kumar

Ravi Kumar

  • 1.7k
  • 28
  • 5.9k

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

Sep 21 2024 6:29 AM

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;

Answers (3)