0

I have been attempting to write a query which retrieves flight information and seat bookings from a couple of tables, my objective is to display the flight details for Cancelled ('C'), Reserved ('R') and the number of available seats. I have managed to get details for the cancelled and Reserved seats but am struggling to calculate the seats available based on this. It seems that because there are two rows the row is resetting the number of seats rather than calulating it correctly. My code is below

SELECT f.FlightID, f.FlightDate, fb.Status, sum(numseats) AS 
NumberOfTotalSeats, maxcapacity - sum(numseats) AS NumberOfAvailiableSeats
FROM Flight f
INNER JOIN FlightBooking fb ON f.FlightID = fb.FlightID
WHERE (f.flightID = 5 AND fb.status = 'R') OR (f.flightID = 5 AND fb.status 
= 'C')
GROUP BY f.FlightID, fb.status

Currently this returns the following:

FlightID, FlightDate,         Status, NumberOfTotalSeats, NumberOfAvailableSeats

   5, 2017-04-07 00:00:00,     C,                 10,                    490
   5 ,2017-04-07 00:00:00,     R,                  2,                    498

I would like the NumberOfAvailableSeats column to display 488 for each one, and to edit my query to allow this. I am using PostgreSQL 9.6

Thanks in advance!

Improve this question
5
  • Your LEFT JOIN executes as as regular INNER JOIN since you have those fb conditions in the WHERE clause. Commented Apr 28, 2017 at 11:56
  • Thanks, have changed the join in the code Commented Apr 28, 2017 at 11:59
  • 2
    Sidenote: Why do you want 2 separate rows? A single row with FlightID, FlightDate, NumberOfReservedSeats, NumberOfCancelledSeats, NumberOfAvailableSeats would be more consumable IMHO. Commented Apr 28, 2017 at 12:00
  • 1
    Are cancelled seats can be reserved again? If yes, is there an ID for a seat (which is possibly the same in those rows of FlightBooking)? (please post the whole table definitions & sample data). Commented Apr 28, 2017 at 12:02
  • stackoverflow.com/questions/43326350/…, stackoverflow.com/questions/43355938/…, stackoverflow.com/questions/29100808/… - all same tables and columns. what is it? Commented Apr 28, 2017 at 12:06

1 Answer 1

Reset to default
0

You want the SUM OVER the two status sums:

SELECT 
  f.FlightID, 
  f.FlightDate, 
  fb.Status, 
  sum(fb.numseats) AS NumberOfTotalSeats, 
  f.maxcapacity - sum(sum(fb.numseats)) over (partition by f.FlightID)
    AS NumberOfAvailiableSeats
FROM Flight f
INNER JOIN FlightBooking fb ON f.FlightID = fb.FlightID
WHERE f.flightID = 5 AND fb.status IN ('R','C') 
GROUP BY f.FlightID, fb.status;

However, as pozs mentioned, that would be much easier to read with a single line:

SELECT 
  f.FlightID, 
  f.FlightDate,
  f.maxcapacity,
  sum(case when fb.Status = 'R' then fb.numseats else 0 end) as reserved,
  sum(case when fb.Status = 'C' then fb.numseats else 0 end) as cancelled,
  f.maxcapacity - sum(fb.numseats) AS NumberOfAvailiableSeats
FROM Flight f
INNER JOIN FlightBooking fb ON f.FlightID = fb.FlightID
WHERE f.flightID = 5 AND fb.status IN ('R','C') 
GROUP BY f.FlightID;

And as pozs asks in another question: does it make sense to subtract all reserved and cancelled seats? So a cancellation blocks a seat just as a reservation does? Maybe you'll have to adjust the algorithm or even the data model.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.