CODEWITHSUNDEEP
sqloracle-database
Enrique Torres
Enrique Torres

Reputation: 289

Count a select distinct with conditions in it

Today I have to write a query for a flight database for school. But right now I'm stuck as I don't know how to continue with it. I need to select the Airlines which have done 1000 or more flights. Because of how we have structured the database, I would have to do it in this way:

SELECT DISTINCT a.nombre, 
                a.codigo, 
                count 
                      ( 
                      select distinct o.salida_prevista, 
                                      o.fecha, 
                                      o.identificador 
                      FROM            operacion o 
                      WHERE           EXISTS 
                                      ( 
                                        SELECT DISTINCT v.identificador, 
                                                        v.codigo_aerolinea 
                                        FROM            vuelo v 
                                        WHERE           v.identificador = o.identificador
                                        AND             v.codigo_aerolinea = a.codigo)) AS numflights
FROM            aerolinea a 
WHERE           numflights > 1000;

But I can't get the COUNT to work, it'll just throw a syntax error at me. How can I make that COUNT work? Thank you in advance! :) EDIT: Creation tables

CREATE TABLE Aerolinea (
codigo_aerolinea                VARCHAR(10) CONSTRAINT PK_Aerolinea         PRIMARY KEY,
nombre                          VARCHAR(75) CONSTRAINT NN_nombre_aerolinea  NOT NULL);

CREATE TABLE Vuelo (
identificador                   NUMBER      CONSTRAINT PK_Vuelo             PRIMARY KEY,
IATA_origen                     VARCHAR(6),
IATA_destino                    VARCHAR(6),
codigo_aerolinea                VARCHAR(10),
codigo                          VARCHAR(6)  CONSTRAINT NN_codigo_vuelo      NOT NULL,
CONSTRAINT FK_IATA_origen                   FOREIGN KEY (IATA_origen)       REFERENCES Aeropuerto(IATA)
                                                                            ON DELETE CASCADE, 
CONSTRAINT FK_IATA_destino                  FOREIGN KEY (IATA_destino)      REFERENCES Aeropuerto(IATA)
                                                                            ON DELETE CASCADE,
CONSTRAINT FK_codigo_aerolinea              FOREIGN KEY (codigo_aerolinea)  REFERENCES Aerolinea(codigo)
                                                                            ON DELETE CASCADE);
CREATE TABLE Operacion(
identificador                   NUMBER,
fecha                           DATE        CONSTRAINT NN_fecha             NOT NULL,
salida_prevista                 NUMBER,
salida_real                     NUMBER,
llegada_prevista                NUMBER      CONSTRAINT NN_llegada_prevista  NOT NULL,
llegada_real                    NUMBER,
numero_cola                     VARCHAR(6),
CONSTRAINT PK_Operacion         PRIMARY KEY (salida_prevista, fecha, identificador),
CONSTRAINT FK_op_vuelo          FOREIGN KEY (identificador)                  REFERENCES Vuelo(identificador)
                                                                            ON DELETE CASCADE,
CONSTRAINT FK_op_numero_cola    FOREIGN KEY(numero_cola)                    REFERENCES Nave(numero_cola)
                                                                            ON DELETE CASCADE);

Upvotes: 0

Views: 52

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Not sure what is the rol of operations.

But looks like just need a join

 SELECT a.nombre, 
        a.codigo
 FROM aerolinea a
 JOIN vuelo v
   ON a.codigo = v.codigo_aerolinea
 JOIN operations o
   ON o.identificador = v.identificador
 GROUP BY a.nombre, a.codigo
 HAVING COUNT(*) > 1000

Upvotes: 1

Related Questions