Kurt Miller Kurt Miller - 7 months ago 13
SQL Question

On a single column : Algebraic operation on Aggregate functions

Is it possible on the same SQL Select Query (no function/procedure nor PL/SQL) to compute an algebraic operation on two aggregate functions ?

| id || A |
|-----||-----|
| 1 || 10 |
| 2 || 20 |
| 3 || 30 |
| 4 || 40 |


SELECT (SELECT SUM(A) FROM Table Where 'id is even') - (SELECT SUM(A) FROM Table Where 'id is odd')
FROM TABLE
WHERE 'condition'


Any idea ?

ps : My databse is Oracle 11g

Answer

You can do it as this:

select sum(case when mod(id,2)=0 then A else 0 end) -
       sum(case when mod(id,2)=0 then 0 else a end)
  from test
 where yourCondition

The SQLFiddle is a MySql sample, since the Oracle also have mod function it should work as is.

See it working here: http://sqlfiddle.com/#!9/1ba756/2

Comments