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!
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.