CODEWITHSUNDEEP
sqlsybase
Alpesh003
Alpesh003

Reputation: 329

How to subtract result of first query and second query

I want to filter out results obtained from two different queries and obtain the total count. Below are my two queries:

    Query 1
    select count(*) 
    from table1 oow
    where oow.status='completed'--134 records

    Query 2
    select count(*) 
    from table2 oow 
    join #temp re
    on oow.order=re.order
    where oow.status='completed'--77 records

The desired result is the subtraction of the two I.e., 134-77=57. How do I achieve that in sybase.

Upvotes: 0

Views: 1829

Answers (4)

Rigel1121
Rigel1121

Reputation: 2016

Try this one:

SELECT
        (select count(*) 
        from table1 oow
        where oow.status='completed')
        -
        (select count(*) 
        from table2 oow 
        join #temp re
        on oow.order=re.order
        where oow.status='completed')

Upvotes: 0

G B
G B

Reputation: 1462

given the table alias oow I'm going to assume that both table1 in query1 and table2 in query2 are actually the same table. if that is the case, you could do it this way:

select count(*) 
from 
    table2 oow 
    left join #temp re
        on oow.order=re.order
where oow.status='completed' 
    and re.order is null

I have used a left join and a check for null instead of performing a subtraction.

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

Try this:

select count(*)
from table1
where not exists
(select 1 
from #temp re
where re.order = order and status = 'Completed')

This query just returns those rows which exist in table1 but do not exist in #temp, based on the order value and filter condition. Therefore, the above is equivalent to getting the total count, the filtered count and the difference between the two.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

use Cross Join

SELECT fst - scd
FROM   (SELECT Count(*) AS fst
        FROM   table1 oow
        WHERE  oow.status = 'completed'),--134 records
       (SELECT Count(*) AS scd
        FROM   table2 oow
               JOIN #temp re
                 ON oow.ORDER = re.ORDER
        WHERE  oow.status = 'completed')

Upvotes: 1

Related Questions