I need help constructing an SQL nested query for a problem I'm trying to solve.

Given these two tables:

airlines_detail:

Bookings:

I need to get this result:

The empty_seats column is a SUM() of the booked columns GROUP BY'd airplane_id. I have this query successfully built already (below), but I need to JOIN or otherwise subquery to get the airline_id column into my result which I'm unclear about.

TO REPRODUCE

1. Go to https://www.mycompiler.io/new/sql
2. Copy and paste the below code and run it to see what I have so far

-- drop tables if exists
DROP TABLE IF EXISTS airlines_detail;
DROP TABLE IF EXISTS bookings;

-- create airlines_detail table
CREATE TABLE airlines_detail (
  airplane_id INTEGER PRIMARY KEY,
  airline_id INTEGER NOT NULL,
  total_seats INTEGER NOT NULL
);

-- create bookings table
CREATE TABLE bookings (
  airplane_id INTEGER NOT NULL,
  booked INTEGER NOT NULL,
  FOREIGN KEY (airplane_id) REFERENCES airlines_detail(airplane_id)
);


-- insert some airlines_detail values
INSERT INTO airlines_detail VALUES (2187, 425, 260);
INSERT INTO airlines_detail VALUES (4361, 747, 290);
INSERT INTO airlines_detail VALUES (3478, 747, 270);
INSERT INTO airlines_detail VALUES (7292, 425, 250);
INSERT INTO airlines_detail VALUES (5833, 425, 310);
INSERT INTO airlines_detail VALUES (3472, 425, 300);
INSERT INTO airlines_detail VALUES (4472, 747, 290);
INSERT INTO airlines_detail VALUES (2624, 425, 320);

-- insert some bookings values
INSERT INTO bookings VALUES (2187, 40);
INSERT INTO bookings VALUES (4361, 30);
INSERT INTO bookings VALUES (4361, 10);
INSERT INTO bookings VALUES (5833, 30);
INSERT INTO bookings VALUES (4361, 30);
INSERT INTO bookings VALUES (2624, 30);
INSERT INTO bookings VALUES (4472, 40);
INSERT INTO bookings VALUES (4472, 40);
INSERT INTO bookings VALUES (2624, 10);
INSERT INTO bookings VALUES (7292, 20);
INSERT INTO bookings VALUES (2187, 20);
INSERT INTO bookings VALUES (4472, 30);
INSERT INTO bookings VALUES (3478, 20);
INSERT INTO bookings VALUES (3472, 40);
INSERT INTO bookings VALUES (4472, 30);
INSERT INTO bookings VALUES (4472, 10);
INSERT INTO bookings VALUES (4361, 20);
INSERT INTO bookings VALUES (3478, 30);
INSERT INTO bookings VALUES (2187, 30);
INSERT INTO bookings VALUES (2187, 10);



-- fetch some values
-- First, sum up total seats booked by airplane_id
SELECT
    airplane_id,
    SUM(booked)
FROM bookings
GROUP BY airplane_id;

-- Need to get airline_id in there somehow too

CodePudding user response：

First of all the number of empty seats is the total number of seats minus the booked ones.

Your query would only return aeroplanes for which at least one record exists in the booking table, but you'd need to return one record for each aeroplane even those for which there are no bookings.

You can get the airline id from the airlines_detail table, and then you can easily add that field to the select list and the group by list. Adding it to the group by list will not create more groups, but it is necessary to have it selected.

So for both of the above reasons, the query should really start out with the airlines_detail table.

With a subquery that would look like this:

SELECT    airplane_id,
          airline_id,
          total_seats - (
                SELECT COALESCE(SUM(booked), 0)
                FROM   bookings b
                WHERE a.airplane_id = b.airplane_id
          ) AS empty_seats
FROM      airlines_detail a
GROUP BY  airplane_id,
          airline_id;

COALESCE is used to deal with the case where there are no bookings. In that case we want to subtract the value 0, avoiding NULL.

Another approach is to join the two tables in a single query. As you want an output for all planes, even when nothing was booked, this join must be an outer join.

So:

SELECT    a.airplane_id,
          a.airline_id,
          a.total_seats - SUM(b.booked) AS empty_seats
FROM      airlines_detail a
LEFT JOIN bookings b
       ON a.airplane_id = b.airplane_id
GROUP BY  a.airplane_id,
          a.airline_id;