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

0
votes

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');

2
sqloracle
Have you tried your query? Have you got an error or an unexpected behaviour? – Aleksej
@Aleksej: was about to type the exact same sentence .. O.o But also adding, just needs to make sure where month = to_date(201505, 'yyyymm') results in a single row ... but .. uh .. yeah .. :) – Ditto
don't post queries in comments, impossible to read .. if it's relevant .. post it in the original post .. – Ditto
You are using a group function (SUM) without any grouping; you need to add a GROUP BY clause, grouping by LS_ASSET_ID; besides, as DItto said, are you sure that the subquery will return exactly ONE value? otherwise you need to modify it with a MIN, MAX or whatever you need – Aleksej

2 Answers

2
votes

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
0
votes

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!