0

So, I have hierarchy table where I want the sum of children (cumulative sum) added to all parent.

I have 2 tables dimension and valuation. I need to sum up volumeimpact based on a dimension tree.

http://sqlfiddle.com/#!15/5c0e7/5

id  dimensionvalueid    name          volume cumulativeSum
1   (null)               one           (null)       700
2   1                    five          200          700
3   2                    sixteen       200          500
4   3                    eighteen      200          300
5   3                    random        100          100
6   (null)               root                       300
7   6                    yellow        100          300
8   6                    orange        100          200
9   8                    green         100          100
JyotiChhetri
  • 289
  • 1
  • 7
  • 21
  • Does this answer your question? [How to get the cumulative sum of children up its parents?](https://stackoverflow.com/questions/73362345/how-to-get-the-cumulative-sum-of-children-up-its-parents) – Alexey Aug 19 '22 at 14:59
  • @Alexey yes, I accepted the answer also. – JyotiChhetri Aug 30 '22 at 17:18

1 Answers1

0

It appears we don't have a handy grouping root column, so we can create it ourselves within your recursive function. Then we can partition by this root column and get our cumulative sum:

WITH RECURSIVE hierarchy AS (
    SELECT
        id,
        name,
        dimensionvalueid,
        id root_id
    FROM
        dimensionvalue d
    WHERE
        dimensionvalueid IS NULL
    UNION ALL
    SELECT
        e.id,
        e.name,
        e.dimensionvalueid,
        hierarchy.root_id
    FROM
        dimensionvalue e,
        hierarchy
    WHERE
        e.dimensionvalueid = hierarchy.id
)
select d.id, d.dimensionvalueid, d.name, v.volume, sum(v.volume) over(partition by root_id order by d.id desc) cumulativeSum
from hierarchy d 
left join valuation v on v.dimensionvalueid = d.id
qaziqarta
  • 1,782
  • 1
  • 4
  • 11
  • I dont think the solution work because the name in the table is written for understanding, the name can be anything. I think the query should use hierarchy path to calculate the cumulative sum – JyotiChhetri Aug 15 '22 at 05:47
  • 1
    @JyotiChhetri: given your sample data, this returns exactly what you asked for: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=3c31da050c9532cba23f5cc5b6723ff1 –  Aug 15 '22 at 08:04
  • @a_horse_with_no_name, yes the query works only if the name is exactly as above. As we know a name is a string and can be anything. The query fails as its partitioning using name – JyotiChhetri Aug 15 '22 at 08:19
  • Then you should provided sample data that shows that right from the beginning. This answers the question you asked initially. –  Aug 15 '22 at 08:23
  • @a_horse_with_no_name, thanks, that "name" column was just so tempting :) – qaziqarta Aug 15 '22 at 08:46
  • @JyotiChhetri, I modified the query, this time no "name" columns were abused! – qaziqarta Aug 15 '22 at 08:47