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
Advertisement
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
Note: In case you want to see every station / airport (without bookings too) you can use
a LEFT JOIN
instead of a INNER JOIN
.