CODEWITHSUNDEEP
sqloracle-databaseplsql
siddharth thiagarajan
siddharth thiagarajan

Reputation: 3

Aggregate in plsql

ORGANIZATION_ID BAY_ID CASCADE_GROUP_ID DOWNSTEAM_VALUE
1001 100012 1 2
1001 100014 1 4
1001 100016 1 6
1001 100018 1 8

I need to create a view by aggregating the values of the DOWNSTEAM_VALUE column mentioned in the above table. In the below example, the aggregation at the DOWNSTEAM_VALUE column should happen by looking at the BAY_ID. If in case, the first row containing BAY_ID is 100012, the downstream value should be calculated by adding up the DOWNSTEAM_VALUE of the current BAY_ID row + remaining DOWNSTEAM_VALUE values in ascending order such as 2+4+6+8 and show like 20 and same goes to next BAY_ID , the downstream value would be 4+6+8=18. Since the last BAY_ID doesn't have any more DOWNSTEAM_VALUE values to add, it should show 8.

ORGANIZATION_ID BAY_ID CASCADE_GROUP_ID DOWNSTEAM_VALUE
1001 100012 1 20
1001 100014 1 18
1001 100016 1 14
1001 100018 1 8

Any help would be really appreciated. Thanks

Upvotes: 0

Views: 147

Answers (1)

Mahamoutou
Mahamoutou

Reputation: 1555

You can use SUM analytic function with windowing clause for that like below.

select ORGANIZATION_ID
    , BAY_ID
    , CASCADE_GROUP_ID
    , sum(DOWNSTEAM_VALUE)over(
        partition by ORGANIZATION_ID, CASCADE_GROUP_ID
          order by BAY_ID asc 
              ROWS BETWEEN CURRENT ROW AND UNBOUNDED
                   FOLLOWING) as DOWNSTEAM_VALUE
from your_table 
;

Upvotes: 1

Related Questions