CODEWITHSUNDEEP
sqloracle-database
pwj21090
pwj21090

Reputation: 25

oracle sum of many rows - the value of a single row

I am trying to do some math in oracle that has me returning the sum of many rows, minus a value of 1 row in a different column on that same table. Is this possible?

For example,

select sum(column1) - (select column2 from table1 where month = to_date(201505, 'yyyymm')) from table1 where month >= to_date(201006, 'yyyymm');

Upvotes: 0

Views: 2428

Answers (2)

Ravindra HV
Ravindra HV

Reputation: 2608

Believe it would be possible. Given that you can identify the row with the other column that has the value to be subtracted you can use the aggregate 'sum' in conjunction with a 'case' statement.

If the condition matches, subtract the relevant column two from column-one else do not subtract and use the original value in the column-one for the 'sum' funcion.

It would look something like this :

select 
sum( 
    case (when <condition-normal>) then 
        column_one 
    else 
        column_two - column_one 
        )
) from table;

PS: Be sure to test it out against different sets of data!

Upvotes: 0

Munir
Munir

Reputation: 3612

It is possible, but you need to cross-join with the subquery.

select
    s.sum1 - t.column2
from (select sum(column1) as sum1 from table1 where month >= to_date('201006','yyyymm') ) s
cross join (select column2 from table1 where month = to_date('201505','yyyymm')) t

Upvotes: 2

Related Questions