postgreSQL - Incorrect query results

Question

I have the following two queries:

Return the number of flights for each manufacturer:

SELECT COUNT(flight) AS num_flights, manufacturer
FROM flights, planes
WHERE flights.tailnum = planes.tailnum
GROUP BY manufacturer
ORDER BY num_flights DESC

That returns (not all results visible here, 27 rows in total):

-------------+---------------------------------+--+--+--+
| num_flights | manufacturer                    |  |  |  |
+-------------+---------------------------------+--+--+--+
| 67623       | "BOEING"                        |  |  |  |
+-------------+---------------------------------+--+--+--+
| 36244       | "AIRBUS INDUSTRIE"              |  |  |  |
+-------------+---------------------------------+--+--+--+
| 11676       | "AIRBUS"                        |  |  |  |
+-------------+---------------------------------+--+--+--+
| 8932        | "MCDONNELL DOUGLAS AIRCRAFT CO" |  |  |  |
+-------------+---------------------------------+--+--+--+
| 4856        | "EMBRAER"                       |  |  |  |
+-------------+---------------------------------+--+--+--+
| 3998        | "MCDONNELL DOUGLAS"             |  |  |  |
+-------------+---------------------------------+--+--+--+
| 1259        | "MCDONNELL DOUGLAS CORPORATION" |  |  |  |
+-------------+---------------------------------+--+--+--+
| 247         | "CESSNA"                        |  |  |  |
+-------------+---------------------------------+--+--+--+
| 162         | "PIPER"                         |  |  |  |
+-------------+---------------------------------+--+--+--+
| 65          | "BELL"                          |  |  |  |
+-------------+---------------------------------+--+--+--+
| 63          | "DEHAVILLAND"                   |  |  |  |
+-------------+---------------------------------+--+--+--+
| 63          | "FRIEDEMANN JON"                |  |  |  |
+-------------+---------------------------------+--+--+--+
| 55          | "STEWART MACO"                  |  |  |  |
+-------------+---------------------------------+--+--+--+
| 54          | "LAMBERT RICHARD"               |  |  |  |
+-------------+---------------------------------+--+--+--+
| 51          | "KILDALL GARY"                  |  |  |  |
+-------------+---------------------------------+--+--+--+
| 47          | "BEECH"                         |  |  |  |
+-------------+---------------------------------+--+--+--+
| 44          | "MARZ BARRY"                    |  |  |  |
+-------------+---------------------------------+--+--+--+
| 42          | "AMERICAN AIRCRAFT INC"         |  |  |  |
+-------------+---------------------------------+--+--+--+
| 40          | "LEBLANC GLENN T"               |  |  |  |
+-------------+---------------------------------+--+--+--+
| 32          | "AGUSTA SPA"                    |  |  |  |
+-------------+---------------------------------+--+--+--+
| 27          | "SIKORSKY"                      |  |  |  |
+-------------+---------------------------------+--+--+--+
| 25          | "PAIR MIKE E"                   |  |  |  |
+-------------+---------------------------------+--+--+--+
| 22          | "DOUGLAS"                       |  |  |  |
+-------------+---------------------------------+--+--+--+
| 19          | "LEARJET INC"                   |  |  |  |
+-------------+---------------------------------+--+--+--+
| 18          | "AVIAT AIRCRAFT INC"            |  |  |  |
+-------------+---------------------------------+--+--+--+
| 17          | "HURLEY JAMES LARRY"            |  |  |  |
+-------------+---------------------------------+--+--+--+
| 13          | "GULFSTREAM AEROSPACE"          |  |  |  |
+-------------+---------------------------------+--+--+--+

And another one:

Return manufacturers with more than 200 planes:

SELECT COUNT(tailnum) AS num_planes, manufacturer 
FROM planes 
GROUP BY manufacturer 
HAVING COUNT(*) > 200 
ORDER BY num_planes DESC

That returns:

+------------+--------------------+
| num_planes | manufacturer       |
+------------+--------------------+
| 1630       | "BOEING"           |
+------------+--------------------+
| 400        | "AIRBUS INDUSTRIE" |
+------------+--------------------+
| 368        | "BOMBARDIER INC"   |
+------------+--------------------+
| 336        | "AIRBUS"           |
+------------+--------------------+
| 299        | "EMBRAER"          |
+------------+--------------------+

Now I am trying to query the number of flights for each manufacturer that has more than 200 planes.

Wrote the following query:

SELECT COUNT(flight) AS num_flights, pl.manufacturer
FROM flights fl, planes pl JOIN
(SELECT COUNT(tailnum) AS num_planes, pl2.manufacturer
    FROM planes pl2
    GROUP BY pl2.manufacturer 
    HAVING COUNT(*) > 200
    ORDER BY num_planes DESC) tm
    ON pl.manufacturer = tm.manufacturer
GROUP BY pl.manufacturer
ORDER BY num_flights DESC

However this query returns incorrect number of flights, and takes ages to execute:

+-------------+--------------------+
| num_flights | manufacturer       |
+-------------+--------------------+
| 262029020   | "BOEING"           |
+-------------+--------------------+
| 64301600    | "AIRBUS INDUSTRIE" |
+-------------+--------------------+
| 59157472    | "BOMBARDIER INC"   |
+-------------+--------------------+
| 54013344    | "AIRBUS"           |
+-------------+--------------------+
| 48065446    | "EMBRAER"          |
+-------------+--------------------+

What am I doing wrong here?

Table structures:

planes:

CREATE TABLE planes
(
     tailnum VARCHAR(6),
     manufacturer VARCHAR(50)
)

+----------+--------------------+
| tailnum  | manufacturer       |
+----------+--------------------+
| "N10156" | "EMBRAER"          |
+----------+--------------------+
| "N102UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N103US" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N104UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N10575" | "EMBRAER"          |
+----------+--------------------+
| "N105UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N107US" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| ...      | ...                |
+----------+--------------------+

flights:

CREATE TABLE flights
(
     flight INT,
     tailnum VARCHAR(6)
)

+--------+----------+
| flight | tailnum  |
+--------+----------+
| 1545   | "N14228" |
+--------+----------+
| 1714   | "N24211" |
+--------+----------+
| 1141   | "N619AA" |
+--------+----------+
| 461    | "N668DN" |
+--------+----------+
| 1696   | "N39463" |
+--------+----------+
| ...    | ...      |
+--------+----------+

Why are you choosing to use archaic syntax instead of proper, explicit, standard, readable JOIN syntax? — Gordon Linoff
– Gordon Linoff, Commented May 28, 2020 at 11:44
@GordonLinoff Which part of the query is not readable? And could you please give me an example of how a proper, explicit, standard and readable JOIN syntax would look like? — RollerMobster
– RollerMobster, Commented May 28, 2020 at 11:51
There's no join-condition between flights and planes in the final query, thus resulting in a cross join. If you switch to JOIN-syntax as Gordon proposed you will notice that, because you get an error message regarding the missing ON — dnoeth
– dnoeth, Commented May 28, 2020 at 11:59
Could you please, substitute the images with text data? This will help in comparisons and got with StackOverflow code of conduct. — William Prigol Lopes
– William Prigol Lopes, Commented May 28, 2020 at 12:21

William Prigol Lopes · Accepted Answer · 2020-05-28 15:57:33Z

1

You can try this (joining planes and flights by tailnum), counting tailnums and flights grouping by manufacturer and filtering by having clause on COUNT(tailnum).

    SELECT manufacturer AS "Manufacturer", 
           COUNT(DISTINCT tailnum) AS "Number of planes",
           COUNT(flights) as "Number of flights",
      FROM planes
INNER JOIN flights
        ON (planes.tailnum=flights.tailnum)
  GROUP BY manufacturer
    HAVING COUNT(DISTINCT tailnum)>200
  ORDER BY 3

edited May 28, 2020 at 15:57

answered May 28, 2020 at 12:27

William Prigol Lopes

1,90519 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RollerMobster Over a year ago

Thanks! But just to double check, your query seems to return manufacturers that have more than 200 flights? What I need is the number of flights for each manufacturers that have more than 200 planes. So the output should contain only 5 rows (see query #2 output) - now it returns 8 (top 8 rows from query #1)

William Prigol Lopes Over a year ago

I've updated the answer, modifying the SELECT. If I understood, you can have the same plane in more than one flight. So, you need to put DISTINCT, to count for the manufacturer, only distinct values from tailnum. Hope that helps you.

Collectives™ on Stack Overflow

postgreSQL - Incorrect query results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related