CODEWITHSUNDEEP
sqlderby
DCR
DCR

Reputation: 15665

derby sql difference between two aggregate values or how to combine two sql statements

The following two queries work by themselves: 

select coalesce(sum(shares*cps),0.0)  from 
transactions
where usr = 1
and (type = 'C'
or type  = 'S')

select coalesce(sum(shares*cps),0.0) from 
transactions
where usr = 1
and (type = 'W'
or type  = 'B')

How do I combine them to get the difference between them? Something like:

select coalesce(sum(a.shares*a.cps),0.0) - coalesce(sum(b.shares*b.cps),0.0)   from 
      (select * from transactions
                where usr = 1
                  and (type = 'C'
                   or type  = 'S')) as a,
      (select * from transactions
                where usr = 1
                  and (type = 'W'
                   or type  = 'B')) as b;

value returned is 0. the first coalesce returns 200000.00 and the second coalesce returns 0.00

Upvotes: 0

Views: 28

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

You can use SYSDUMMY1, a dummy table with one record that can be used to select simple values, in this case the difference between the two sums:

select 
( select coalesce(sum(shares*cps),0.0)  from 
  transactions
  where usr = 1
  and (type = 'C'
       or type  = 'S')) -
( select coalesce(sum(shares*cps),0.0) from 
  transactions
  where usr = 1
  and (type = 'W'
       or type  = 'B')) as DIFFERENCE
from SYSIBM.SYSDUMMY1

Upvotes: 2

Related Questions