co-pilots who made the largest number of flights [closed]

Question

co-pilots who made the largest number of flights [closed]

Navigation

#1 by (2 votes)
#2 by (1 votes)

-3

I have this question:

Name and number of flight hours of co-pilots who have flown the most flights. It is intended to know the exact number of flights made by each of these co-pilots

And I tried to solve it like this:

SELECT t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
FROM   Tripulante t , Piloto p1 , Voo v
WHERE  t.id = v.id_copiloto
   AND v.id_copiloto = p1.id
        GROUP BY ( t.apelido )
        HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
        FROM Voo v2)

However, my output is this

anditshouldbejustCarlosbecauseheistheco-pilotwhomademoreflights.WhatamIdoingwrong?OrhowdoIsolvethisquestion?

Tables:

sql

asked by anonymous 09.12.2015 / 18:08

2 answers

SQL error while trying to make an UPDATE with PHP [closed] Automatic name generator

score 2 · Answer 1

If it's SLQServer looks like this:

SELECT TOP 1 t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
FROM   Tripulante t , Piloto p1 , Voo v
WHERE  t.id = v.id_copiloto
   AND v.id_copiloto = p1.id
        GROUP BY ( t.apelido )
        HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
        FROM Voo v2)

MySQL I think it uses limit:

    SELECT t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
        FROM   Tripulante t , Piloto p1 , Voo v
        WHERE  t.id = v.id_copiloto
           AND v.id_copiloto = p1.id
                GROUP BY ( t.apelido )
                HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
                FROM Voo v2)
        Limit 1

Just check if it is not better for you to use an Order By

score 1 · Answer 2

To know total flights (cycles):

SELECT TMP.* 
  FROM (SELECT T.Id, T.Nome, Count(*) Voos
          FROM Tripulante T
          JOIN Voo V ON V.Id_CoPiloto = T.Id
         GROUP BY T.Id, T.Nome) TMP
 ORDER BY TMP.Voos DESC

To know total hours:

SELECT TMP.* 
  FROM (SELECT T.Id, T.Nome, Sum(V.Data_Chegada - V.Data_Partita) Horas
          FROM Tripulante T
          JOIN Voo V ON V.Id_CoPiloto = T.Id
         GROUP BY T.Id, T.Nome) TMP
 ORDER BY TMP.Horas DESC

I did not test, but it should be good.