Merging and counting 2 tables in SQL

I’ve been trying forever to find a suitable answer so I would really appreciate some help.

I have 2 tables that I want to merge…

Table1 contains airport/station data:

id  | name     | country
----+----------+--------
ABC | Paris    | France
DEF | New York | USA
GHI | Madrid   | Spain

Table2 contains booking data:

bid | origin | destination | vehicle
----+--------+-------------+--------
123 | ABC    | XYZ         | plane
456 | XYZ    | GHI         | train
789 | GHI    | XYZ         | bus
999 | DEF    | ABC         | plane

I would like to have a finished table that would display how many bookings we have on each airport/station per vehicle so it could look like this:

id  | name     | country | bookings | plane | train | bus
----+----------+---------+----------+-------+-------+----
ABC | Paris    | France  | 2        | 2     | 0     | 0
DEF | New York | USA     | 1        | 1     | 0     | 0        
GHI | Madrid   | Spain   | 2        | 0     | 1     | 1      

Is there any way to do this?

So far I’m here

SELECT a.id, a.name, a.country, vehicle_type, count(bid)
FROM Table2
LEFT JOIN Table1 a ON a.id = origin <> destination
GROUP BY id, name, country, vehicle_type

Answer

You can use the following solution using SUM with CASE to count the vehicles in different columns:

SELECT t1.id, t1.name, t1.country,
  COUNT(bid) AS bookings,
  SUM(CASE WHEN t2.vehicle = 'plane' THEN 1 ELSE 0 END) AS plane,
  SUM(CASE WHEN t2.vehicle = 'train' THEN 1 ELSE 0 END) AS train,
  SUM(CASE WHEN t2.vehicle = 'bus' THEN 1 ELSE 0 END) AS bus
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.origin OR t1.id = t2.destination
GROUP BY t1.id, t1.name, t1.country
ORDER BY t1.id ASC

demo on dbfiddle.uk

Note: In case you want to see every station / airport (without bookings too) you can use a LEFT JOIN instead of a INNER JOIN.