Kurt Miller - 8 months ago 23

SQL Question

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