CODEWITHSUNDEEP
oracle-database
Machindra
Machindra

Reputation: 67

Sum rows and duplicate the values

I have below records in my oracle database table

Id     Amount1  Amount2 Amount3
1101    10       20     30
1102    15       10     20
1103    20       30     35
1104    40       50     60
1105    70       80     90

I would like to sum 2nd (1102) and 3rd row (1103) and show the same sum in 2nd and 3rd row. My result should be as below. How we can be achieved this in oracle query?

Id  Amount1 Amount2 Amount3
1101    10  20      30
1102    35  40      55
1103    35  40      55
1104    40  50      60
1105    70  80      90

Thank you.

Upvotes: 1

Views: 99

Answers (1)

GMB
GMB

Reputation: 222612

Here is one way to do it, using analytic functions. Assuming that id is a unique column with values greater than 0:

select
    id,
    amount1,
    amount2,
    sum(amount3) over(partition by case when id in (1102, 1103) then 0 else id end) amount3
from mytable
order by id

The window sum() assigns a special partition to record where id is 1102 or 1103, so they are sumed together. Other records remain in their own partition.

Demo on DB Fiddle:

  ID | AMOUNT1 | AMOUNT2 | AMOUNT3
---: | ------: | ------: | ------:
1101 |      10 |      20 |      30
1102 |      15 |      10 |      55
1103 |      20 |      30 |      55
1104 |      40 |      50 |      60
1105 |      70 |      80 |      90

Upvotes: 1

Related Questions