How to get minus off to the the difference?-CodePudding

I'm trying to find the available seat. I try using the following code, to get the total row after that minus the value at capacity. But it fails to do so. May I know what is the correct way to do it ?

 SELECT r.Route_No, s.Serial_No, s.Flight_Fly_On, a.Capacity, (Capacity - (count(*) FROM scheduled) AS Available_Seat
    FROM Route r, Scheduled s, Airplane a
    WHERE s.Serial_No = a.Serial_No
    AND r.Route_No = s.Route_No;

CodePudding user response：

Assuming that your SCHEDULED table counts when a person is scheduled on a particular flight and that you can uniquely identify a flight using the route, airplane's serial number and the flight date then you can us an analytic COUNT function (rather than using a sub-query):

SELECT r.Route_No,
       s.Serial_No,
       s.Flight_Fly_On,
       a.Capacity,
       a.Capacity - COUNT(*) OVER (PARTITION BY s.route_no, s.serial_no, s.flight_fly_on)
         AS Available_Seat
FROM   Route r
       INNER JOIN Scheduled s
       ON (r.Route_No = s.Route_No)
       INNER JOIN Airplane a
       ON (s.Serial_No = a.Serial_No);

If you did want to use a sub-query then you need to correlate it to the outer query:

SELECT r.Route_No,
       s.Serial_No,
       s.Flight_Fly_On,
       a.Capacity,
       a.Capacity - (SELECT COUNT(*)
                     FROM   Scheduled c
                     WHERE  s.route_no      = c.route_no
                     AND    s.serial_no     = c.serial_no
                     AND    s.flight_fly_on = c.flight_fly_on)
         AS Available_Seat
FROM   Route r
       INNER JOIN Scheduled s
       ON (r.Route_No = s.Route_No)
       INNER JOIN Airplane a
       ON (s.Serial_No = a.Serial_No);

If it is not the SCHEDULED table that holds details of a person's bookings but it is, for example, the BOOKINGS table then you are using the wrong table and need to count from the correct table:

SELECT r.Route_No,
       s.Serial_No,
       s.Flight_Fly_On,
       a.Capacity,
       a.Capacity - (SELECT COUNT(*)
                     FROM   Bookings b
                     WHERE  b.scheduled_no = s.scheduled_no)
         AS Available_Seat
FROM   Route r
       INNER JOIN Scheduled s
       ON (r.Route_No = s.Route_No)
       INNER JOIN Airplane a
       ON (s.Serial_No = a.Serial_No);

CodePudding user response：

Please test the following query.
I have normalised the JOIN conditions, specified that we are counting the schedule table, and added GROUP BY.
I have also added the column count(s.Route_No) AS seats_booked so that, if the query doesn't return the expected results you can check whether the COUNT() is correct. This will help find the problem if there is one.

SELECT 
  r.Route_No, 
  s.Serial_No, 
  s.Flight_Fly_On, 
  a.Capacity AS total_seats, 
  COUNT(s.Route_No) AS seats_booked
  (a.Capacity - (count(s.Route_No) FROM scheduled) AS Available_Seats
FROM Route r 
JOIN Scheduled s ON r.Route_No = s.Route_No
JOIN Airplane a on s.Serial_No = a.Serial_No
GROUP BY
  r.Route_No, 
  s.Serial_No, 
  s.Flight_Fly_On, 
  a.Capacity ;