CODEWITHSUNDEEP
sqldatabase
manohjay
manohjay

Reputation: 110

How can I subtract two queries in sql?

my first query returns me the value 20:

    SELECT numberofseats
    from plane
    where tail number in
     ( select flighttailnumberfk
       from flight
       where departuretime between '11/29/2014' and '11/30/2014' and
             flightdepartureairportfk = 'jfk' and
             flightarrivalairport = 'mli'
     )

and my second query returns me 1:

    select count(reservationseatfk)
    from flight f, reservation r
    where f.departuretime between '11/29/2014' and '11/30/2014' and
          f.reservationflightidfk = f.flightid and r.reservationdeparturetimefk = f.departuretime

Now my problem is that I want to subtract the first query from the second query and give me the answer 19. how do i do that?

Upvotes: 3

Views: 8608

Answers (2)

hkutluay
hkutluay

Reputation: 6944

You could use sub sql to subtract

SELECT numberofseats - ( select count(reservationseatfk)
    from flight f, reservation r
    where f.departuretime between '11/29/2014' and '11/30/2014' and
          f.reservationflightidfk = f.flightid and r.reservationdeparturetimefk = f.departuretime)
    from plane
    where tail number in
     ( select flighttailnumberfk
       from flight
       where departuretime between '11/29/2014' and '11/30/2014' and
             flightdepartureairportfk = 'jfk' and
             flightarrivalairport = 'mli'
     )

Upvotes: 3

amit dayama
amit dayama

Reputation: 3326

you can do it in following way: 

SELECT (query1) - (query2)

or 

SELECT (query1)  - (query2) AS Difference

or 

select @result = (query1) - (query2)

Upvotes: 3

Related Questions