SQL show available, reserved and cancelled seats

Basically, I need to show the number of reserved , cancelled and available seats for a train.

I have this for the available seats:

 SELECT t.trainid, t.maxcapacity - coalesce(sum(b.numseats), 0) as availableseats 
FROM Train t
left JOIN TrainBooking b
ON t.TrainID = b.TrainID
WHERE b.Status IN ('r')
GROUP BY t.TrainID , t.maxcapacity 

In the trainbooking table, I have a column called “status”. Status is either reserved or cancelled. I need to display the reserved and cancelled seats with the available seats, but i’m not sure how to do this.

Example data of the two tables:

Train:

TrainId maxseats 
1        10
2        10

Trainbooking:

bookingid  trainid status   numseats 
1            1      'r'       2
2            1      'c'       2
3            2      'r'       3

Results:

trainid availableseats  cancelledseats reservedseats
1           8                2             2
2           7                0             3

I’m new to SQL so any help would be appreciated – thanks.

Answer

Try the following, here is the demo.

select
    trainId,
    (maxSeats - reservedSeats) as availableSeats,
    cancelledSeats,
    reservedSeats
from
(
  select
    t.trainId,
    maxSeats,
    sum(case when status = 'c' then numseats else 0 end) as cancelledSeats,
    sum(case when status = 'r' then numseats else 0 end) as reservedSeats
  from train t
  join trainBooking tb
  on t.trainId = tb.trainId
  group by
      t.trainId,
      maxSeats
) vals  
order by
    trainId

Output:

| trainid | availableSeats | cancelledSeats| reservedSeats |
| ------- | -------------- | ------------- | ------------- |
| 1       | 8              | 2             | 2             |
| 2       | 7              | 0             | 3             |