Different query in the where section, how to transform into 1 query?

Question

I have two simple queries

select sum(deb)-sum(cre) as result1 from CXC where id='22731999' 

select sum(deb)-sum(cre) as result2 from CXC where id='22731999' and tipo='IM'

the difference is the where, for example the first query results in 769686 and the second in 3469, what I have to do, to see the result of the two queries in one result?

result1  result2
----------------
769686   3469

I tried

select sum(C.deb)-sum(C.cre) as Result1 from CXC C where C.id='22731999'
UNION
select sum(X.deb)-sum(X.cre) as Result2 from CXC X where X.id='22731999' and .tipo='IM'

but this is not what I want because it results in 2 rows

result1
result2

I must say that I used this query under an ODBC bridge to connect Cobol files, the driver is tooo old.. so the sql is quite basic.. I have this limitation :(

the name of the driver is Relational DataBridge for RM Cobol

Answer 1

Try a self join. It would be something like this:

select sum(C.deb)-sum(C.cre) as Result1, sum(X.deb)-sum(X.cre) as Result2
from CXC C, CXC X
where C.id='22731999' and X.id='22731999' and .tipo='IM'

Answer 2

Give this a try

SELECT max(Result1) as Result1, max(Result2) as Result2
FROM
    (
    SELECT 
    select sum(C.deb)-sum(C.cre) as Result1, null as Result2
    from CXC C where C.id='22731999'
    UNION
    select null as Result1, sum(X.deb)-sum(X.cre) as Result2 
    from CXC X where X.id='22731999' and .tipo='IM'
    ) x

Answer 3

Use:

SELECT SUM(t.deb) - SUM(t.cre) as result1,
       (SELECT SUM(x.deb) - SUM(x.cre) 
          FROM CXC x
         WHERE x.id = t.id
           AND x.tipo = 'IM') AS result2
  FROM CXC t
 WHERE t.id = '22731999' 

...to get:

result1  result2
----------------
769686   3469

Answer 4

I think of it like this... union puts queries on top of each other. joins put queries next to each other.

What I do in this situation is this...

SELECT result1, result2 FROM
  (select sum(deb)-sum(cre) as result1, id from CXC where id='22731999' ) query1
JOIN
(select sum(deb)-sum(cre) as result2, id from CXC where id='22731999' and tipo='IM') query2
 ON query1.id = query2.id

To simplify what's going on here, just pretend that query1 is a table, and query2 is a table

select result1, result2 
from query1
join query2 on query1.id = query2.id

Hope this helps.