Subtracting two values from SQL queries (Oracle)-CodePudding

I am trying to subtract the value of count routeid from capacity. It should be 350 - 1, I have no luck doing it code below

(SELECT CAPACITY 
 FROM PLANE 
 WHERE SERIALNO = 'FH-FBT') -  
(SELECT COUNT(ROUTEID)
 FROM RESERVATION 
 WHERE ROUTEID = 'FBN001')

CodePudding user response：

A query result is a table. In your case you have two query results, and the tables consist of one row and column each. You subtract one result set from the other. I.e. you have a statement that is dataset - dataset or, as these are scalar queries value - value. The DBMS, however does not know what to do with this, because you haven't told it to select the result.

With a capacity of 350 and a reservation count of 50 you have a statement like this:

350 - 50

But a proper statement would look like this in standard SQL:

select 350 - 50;

And as Oracle always needs a table to select from:

select 350 - 50 from dual;

The whole query:

SELECT 
  (SELECT capacity FROM plane WHERE serialno = 'FH-FBT') -  
  (SELECT COUNT(routeid) FROM reservation WHERE routed = 'FBN001')
FROM DUAL;

Another option to select the result would be to cross join the two values / subquery results:

SELECT capacity - reserved
FROM
  (SELECT capacity FROM plane WHERE serialno = 'FH-FBT')
CROSS JOIN
  (SELECT COUNT(routeid) AS reserved FROM reservation WHERE routed = 'FBN001');

CodePudding user response：

You can do it like this, if I understood correctly what you want:

SELECT CAPACITY -(SELECT COUNT(ROUTEID)
 FROM RESERVATION 
 WHERE ROUTEID = 'FBN001') 
 FROM PLANE 
 WHERE SERIALNO = 'FH-FBT'

SELECT capacity -reserved_count
    FROM PLANE 
     WHERE SERIALNO = 'FH-FBT'  join
    (SELECT COUNT(ROUTEID)reserved_count
     FROM RESERVATION 
     WHERE ROUTEID = 'FBN001')