problem with getting result with my query!!!

Roman10 · Dec 2, 2013

Hi

I have 3 tables which are (flight, model, airplane)
Flight: fid, pid, aid, plannedDeparture, actualDeparture, passengerCount, destination
Model: mid, name
Airplane: aid, mid, oid, hid, dateAqu

and I want to get a report to show the total number of flights and the average passenger count for all completed flights grouped by Model.id and sorted by the average passenger count. Include Model.name into this report.

I tried this query, but it doesn't work. it made me crazy. I hope if there is someone can help me here

select distinct flight.fid, flight.aid, flight.pid, flight.plannedDeparture, flight.actualDeparture,
flight.passengerCount, flight.destination, airplane.aid, airplane.mid, model.mid, model.name
from flight, model, airplane
where flight.aid = airplane.aid and airplane.mid = model.mid
GROUP BY model.mid
and (select COUNT(flight.fid) as `count`, AVG(flight.passengerCount) as `average` FROM flight);

jpadie · Dec 2, 2013

difficult to test the answer without sample data (provide this in sql insert format) but off the top of my head something like this may be what you are looking for (assuming that actualDeparture is formatted as a datetime field.)

Code:

SELECT		a.mid, m.`name`, COUNT(f.fid) AS `Total Flights`, AVG(f.passengerCount) AS `Average Passenger Count`
FROM 		airplane a
JOIN		flight f
ON		a.mid = m.mid
JOIN		flight f
ON		a.mid = f.aid
WHERE		actualDeparture < now()
GROUP BY	a.mid ASC, m.`name` ASC

Roman10 · Dec 2, 2013

Hi jpadie

I tried your solution, but it doesn't work

Roman10 · Dec 2, 2013

This is my schema:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `SADB` ;
CREATE SCHEMA IF NOT EXISTS `SADB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `SADB` ;

-- -----------------------------------------------------
-- Table `SADB`.`Model`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Model` (
`mid` INT NOT NULL,
`name` VARCHAR(45) NULL,
`usefulLoad` FLOAT NULL,
`pilotCount` INT,
PRIMARY KEY (`mid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Person` (
`pid` INT NOT NULL,
`firstName` VARCHAR(64) NULL,
`lastName` VARCHAR(256) NULL,
`email` VARCHAR(128) NULL,
`phone` VARCHAR(24) NULL,
PRIMARY KEY (`pid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Pilot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Pilot` (
`pid` INT NOT NULL,
`hireDate` DATE NULL,
PRIMARY KEY (`pid`),
INDEX `fk_Pilot_Person1_idx` (`pid` ASC),
CONSTRAINT `fk_Pilot_Person1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Person` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`License`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`License` (
`pid` INT NOT NULL,
`mid` INT NOT NULL,
`licenseDate` DATE NULL,
PRIMARY KEY (`pid`, `mid`),
INDEX `fk_Pilot_has_Model_Model1_idx` (`mid` ASC),
INDEX `fk_Pilot_has_Model_Pilot1_idx` (`pid` ASC),
CONSTRAINT `fk_Pilot_has_Model_Pilot1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Pilot` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Pilot_has_Model_Model1`
FOREIGN KEY (`mid`)
REFERENCES `SADB`.`Model` (`mid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- Data:
-- Table Model:
Insert into Model(mid,name,pilotCount,usefulLoad) Values(1,'Cessna Skyhawk',1,917);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(2,'Piper Archer TX',1,870);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(3,'De Havilland DHC-6 Twin Otter',2,4400);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(4,'Jet Prop Commander AC-695A',2,3950);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(5,'King Air 350, B300CER',2,6033);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(6,'Piper Sport Cruiser',1,707);

-- Person Table:
Insert into Person(pid,firstName,lastName,email,phone) Values(1,'Amy','Amyson','a@amy.com','413-366-5196');
Insert into Person(pid,firstName,lastName,email,phone) Values(2,'Tom','Tomson','t@tom.com','413-885-3670');
Insert into Person(pid,firstName,lastName,email,phone) Values(3,'Don','Donson','d@don.com','413-309-3501');
Insert into Person(pid,firstName,lastName,email,phone) Values(4,'Eve','Eveson','e@eve.com','413-870-5178');
Insert into Person(pid,firstName,lastName,email,phone) Values(5,'Pat','Patson','p@pat.com','413-464-4202');
Insert into Person(pid,firstName,lastName,email,phone) Values(6,'Kim','Kimson','k@kim.com','413-613-3281');
Insert into Person(pid,firstName,lastName,email,phone) Values(7,'Ann','Annson','a@ann.com','413-626-3890');
Insert into Person(pid,firstName,lastName,email,phone) Values(8,'Wes','Wesson','w@wes.com','413-284-5235');
Insert into Person(pid,firstName,lastName,email,phone) Values(9,'Joe','Joeson','j@joe.com','413-822-3697');
Insert into Person(pid,firstName,lastName,email,phone) Values(10,'Jan','Janson','j@jan.com','413-445-4628');
Insert into Person(pid,firstName,lastName,email,phone) Values(11,'Sue','Sueson','s@sue.com','413-667-4260');
Insert into Person(pid,firstName,lastName,email,phone) Values(12,'Ron','Ronson','r@ron.com','413-520-4715');
Insert into Person(pid,firstName,lastName,email,phone) Values(13,'Ben','Benson','b@ben.com','617-532-5193');
Insert into Person(pid,firstName,lastName,email,phone) Values(14,'Elf','Elfson','e@elf.com','212-842-4802');

-- Pilot Table:
Insert into Pilot(pid,hireDate) Values(1,'2009-12-03');
Insert into Pilot(pid,hireDate) Values(6,'2008-01-04');
Insert into Pilot(pid,hireDate) Values(7,'2010-12-15');
Insert into Pilot(pid,hireDate) Values(8,'2011-05-06');
Insert into Pilot(pid,hireDate) Values(9,'2007-06-07');
Insert into Pilot(pid,hireDate) Values(10,'2009-03-08');
Insert into Pilot(pid,hireDate) Values(11,'2004-07-09');
Insert into Pilot(pid,hireDate) Values(12,'2005-11-10');
Insert into Pilot(pid,hireDate) Values(13,'2013-11-11');
Insert into Pilot(pid,hireDate) Values(14,'2013-11-12');

-- License Table:
Insert into License(pid,mid,licenseDate) Values(1,5,'2009-07-01');
Insert into License(pid,mid,licenseDate) Values(6,3,'2007-04-24');
Insert into License(pid,mid,licenseDate) Values(7,1,'2009-12-25');
Insert into License(pid,mid,licenseDate) Values(8,2,'2009-10-28');
Insert into License(pid,mid,licenseDate) Values(9,3,'2004-07-17');
Insert into License(pid,mid,licenseDate) Values(10,4,'2004-12-09');
Insert into License(pid,mid,licenseDate) Values(11,5,'2001-10-13');
Insert into License(pid,mid,licenseDate) Values(12,2,'2005-04-24');
Insert into License(pid,mid,licenseDate) Values(7,3,'2009-12-26');
Insert into License(pid,mid,licenseDate) Values(8,1,'2009-10-28');
Insert into License(pid,mid,licenseDate) Values(10,3,'2004-12-11');
Insert into License(pid,mid,licenseDate) Values(11,2,'2001-10-08');
Insert into License(pid,mid,licenseDate) Values(13,6,'2013-11-12');
Insert into License(pid,mid,licenseDate) Values(14,6,'2013-11-12');

jpadie · Dec 3, 2013

Hi

you have not provided the flights table. nor the aircraft table. both of which are important for your query.

as it happens I have written a suite of about 40 reports on similar datasets for a flight planning software company. Strange coincidence!

jpadie · Dec 3, 2013

in case it helps, my original query was incorrect in that it referenced the flights table twice. the first join should have been to model m

I created some dummy tables and data as follows

Code:

<?php
$host = '127.0.0.1';
$port = 8889;
$dbName = 'SADB';
$user = 'root';
$pass = '';

$pdo = new PDO ("mysql:host=$host;port=$port;dbname=$dbName;", $user, $pass);


//create some tables
$pdo->exec('
CREATE TABLE `aircraft` (
 `aid` int(10) NOT NULL AUTO_INCREMENT,
 `mid` int(10) DEFAULT NULL,
 PRIMARY KEY (`aid`)
)
'
);
$pdo->exec('
CREATE TABLE `flights` (
 `flightid` int(10) NOT NULL AUTO_INCREMENT,
 `aid` int(10) DEFAULT NULL,
 `departureTime` datetime DEFAULT NULL,
 `passengerCount` int(4) DEFAULT NULL,
 PRIMARY KEY (`flightid`)
)
');


//now insert some dummy data
$s = $pdo->prepare('insert into aircraft (aid, mid) values (?,?)');
if($s === false):
	print_r($pdo->errorInfo());
	die;
endif;
//create 20 aircraft
for($i=1; $i<=20; $i++):
	$mid = rand(1,6);
	$result = $s->execute(array(NULL, $mid));
	if($result === false):
		print_r($s->errorInfo());
		die;
	endif;
endfor;

//create 500 random flights
$s = $pdo->prepare('insert into flights (flightid, aid, departureTime, passengerCount) values (?,?,?,?)');
if($s === false):
	print_r($pdo->errorInfo());
	die;
endif;
date_default_timezone_set('UTC');

for($i=1; $i<=500; $i++):
	$aid = rand(1,20);
	$plus = rand(1,2) === 1 ? '+ ' : '- ';
	$hours = $plus . rand(1,10000) . ' hours'; //space the departure times out
	$departureTime = date('Y-m-d H:i:s', strtotime($hours));
	$passengerCount = rand(5, 400); //randomise numbers of passengers
	$result = $s->execute(array(NULL, $aid, $departureTime, $passengerCount));
	if(!$result):
		print_r($s->errorInfo());
	endif;
endfor;
?>

then ran this query
[code]
SELECT          YEAR(departureTime), 
                MONTH(departureTime), 
                a.mid, 
                m.`name`,      
                COUNT(f.flightid) AS `Total Flights`, 
                SUM(f.passengerCount) as `Total Passengers`, 
                AVG(f.passengerCount) AS `Average Passenger Count`
FROM            aircraft a
JOIN            Model m
ON              a.mid = m.mid
JOIN            flights f
ON              a.aid = f.aid
WHERE           YEAR(departureTime) IN (2012,2013)
GROUP BY        YEAR(departureTime) ASC, 
                MONTH(departureTime) ASC
                a.mid ASC, 
                m.`name` ASC,

and got a table out exactly as I was expecting. broken down by aircraft type within each month of each of 2012 and 2013.

is this what you were looking for?

Roman10 · Dec 3, 2013

Hi bro

here is my full schema

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `SADB` ;
CREATE SCHEMA IF NOT EXISTS `SADB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `SADB` ;

-- -----------------------------------------------------
-- Table `SADB`.`Model`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Model` (
`mid` INT NOT NULL,
`name` VARCHAR(45) NULL,
`usefulLoad` FLOAT NULL,
`pilotCount` INT,
PRIMARY KEY (`mid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Owner`
-- ----------------------------------------------------- INSERT INTO Owner(oid) Values(1);
CREATE TABLE IF NOT EXISTS `SADB`.`Owner` (
`oid` INT NOT NULL,
PRIMARY KEY (`oid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Hangar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Hangar` (
`hid` INT NOT NULL,
`name` VARCHAR(64) NULL,
`capacity` INT NULL,
PRIMARY KEY (`hid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Airplane`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Airplane` (
`aid` INT NOT NULL,
`mid` INT NOT NULL,
`oid` INT NOT NULL,
`hid` INT NOT NULL,
`dateAcquired` DATE NULL,
`lastRepairDate` DATE,
PRIMARY KEY (`aid`),
INDEX `fk_Airplane_Model_idx` (`mid` ASC),
INDEX `fk_Airplane_Owner1_idx` (`oid` ASC),
INDEX `fk_Airplane_Hangar1_idx` (`hid` ASC),
CONSTRAINT `fk_Airplane_Model`
FOREIGN KEY (`mid`)
REFERENCES `SADB`.`Model` (`mid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Airplane_Owner1`
FOREIGN KEY (`oid`)
REFERENCES `SADB`.`Owner` (`oid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Airplane_Hangar1`
FOREIGN KEY (`hid`)
REFERENCES `SADB`.`Hangar` (`hid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Person` (
`pid` INT NOT NULL,
`firstName` VARCHAR(64) NULL,
`lastName` VARCHAR(256) NULL,
`email` VARCHAR(128) NULL,
`phone` VARCHAR(24) NULL,
PRIMARY KEY (`pid`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`IndividualOwner`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`IndividualOwner` (
`oid` INT NOT NULL,
`pid` INT NOT NULL,
PRIMARY KEY (`oid`),
INDEX `fk_PersonOwner_Person1_idx` (`pid` ASC),
CONSTRAINT `fk_PersonOwner_Owner1`
FOREIGN KEY (`oid`)
REFERENCES `SADB`.`Owner` (`oid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_PersonOwner_Person1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Person` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`CommercialOwner`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`CommercialOwner` (
`oid` INT NOT NULL,
`contactId` INT NOT NULL,
PRIMARY KEY (`oid`),
INDEX `fk_CompanyOwner_Person1_idx` (`contactId` ASC),
CONSTRAINT `fk_CompanyOwner_Owner1`
FOREIGN KEY (`oid`)
REFERENCES `SADB`.`Owner` (`oid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_CompanyOwner_Person1`
FOREIGN KEY (`contactId`)
REFERENCES `SADB`.`Person` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Pilot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Pilot` (
`pid` INT NOT NULL,
`hireDate` DATE NULL,
PRIMARY KEY (`pid`),
INDEX `fk_Pilot_Person1_idx` (`pid` ASC),
CONSTRAINT `fk_Pilot_Person1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Person` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`License`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`License` (
`pid` INT NOT NULL,
`mid` INT NOT NULL,
`licenseDate` DATE NULL,
PRIMARY KEY (`pid`, `mid`),
INDEX `fk_Pilot_has_Model_Model1_idx` (`mid` ASC),
INDEX `fk_Pilot_has_Model_Pilot1_idx` (`pid` ASC),
CONSTRAINT `fk_Pilot_has_Model_Pilot1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Pilot` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Pilot_has_Model_Model1`
FOREIGN KEY (`mid`)
REFERENCES `SADB`.`Model` (`mid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SADB`.`Flight`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `SADB`.`Flight` (
`fid` INT NOT NULL,
`aid` INT NOT NULL,
`pid` INT NOT NULL,
`plannedDeparture` DATETIME NULL,
`actualDeparture` DATETIME NULL,
`passengerCount` INT NULL,
`destination` VARCHAR(45) NULL,
PRIMARY KEY (`fid`),
INDEX `fk_Flight_Airplane1_idx` (`aid` ASC),
INDEX `fk_Flight_Pilot1_idx` (`pid` ASC),
CONSTRAINT `fk_Flight_Airplane1`
FOREIGN KEY (`aid`)
REFERENCES `SADB`.`Airplane` (`aid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Flight_Pilot1`
FOREIGN KEY (`pid`)
REFERENCES `SADB`.`Pilot` (`pid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- Data:
-- Table Model:
Insert into Model(mid,name,pilotCount,usefulLoad) Values(1,'Cessna Skyhawk',1,917);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(2,'Piper Archer TX',1,870);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(3,'De Havilland DHC-6 Twin Otter',2,4400);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(4,'Jet Prop Commander AC-695A',2,3950);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(5,'King Air 350, B300CER',2,6033);
Insert into Model(mid,name,pilotCount,usefulLoad) Values(6,'Piper Sport Cruiser',1,707);

-- Table Owner:
Insert into Owner(oid) Values(100);
Insert into Owner(oid) Values(200);
Insert into Owner(oid) Values(300);
Insert into Owner(oid) Values(400);
Insert into Owner(oid) Values(500);

-- Person Table:
Insert into Person(pid,firstName,lastName,email,phone) Values(1,'Amy','Amyson','a@amy.com','413-366-5196');
Insert into Person(pid,firstName,lastName,email,phone) Values(2,'Tom','Tomson','t@tom.com','413-885-3670');
Insert into Person(pid,firstName,lastName,email,phone) Values(3,'Don','Donson','d@don.com','413-309-3501');
Insert into Person(pid,firstName,lastName,email,phone) Values(4,'Eve','Eveson','e@eve.com','413-870-5178');
Insert into Person(pid,firstName,lastName,email,phone) Values(5,'Pat','Patson','p@pat.com','413-464-4202');
Insert into Person(pid,firstName,lastName,email,phone) Values(6,'Kim','Kimson','k@kim.com','413-613-3281');
Insert into Person(pid,firstName,lastName,email,phone) Values(7,'Ann','Annson','a@ann.com','413-626-3890');
Insert into Person(pid,firstName,lastName,email,phone) Values(8,'Wes','Wesson','w@wes.com','413-284-5235');
Insert into Person(pid,firstName,lastName,email,phone) Values(9,'Joe','Joeson','j@joe.com','413-822-3697');
Insert into Person(pid,firstName,lastName,email,phone) Values(10,'Jan','Janson','j@jan.com','413-445-4628');
Insert into Person(pid,firstName,lastName,email,phone) Values(11,'Sue','Sueson','s@sue.com','413-667-4260');
Insert into Person(pid,firstName,lastName,email,phone) Values(12,'Ron','Ronson','r@ron.com','413-520-4715');
Insert into Person(pid,firstName,lastName,email,phone) Values(13,'Ben','Benson','b@ben.com','617-532-5193');
Insert into Person(pid,firstName,lastName,email,phone) Values(14,'Elf','Elfson','e@elf.com','212-842-4802');

-- IndividualOwner Table:
Insert into IndividualOwner(oid,pid) Values(100,1);
Insert into IndividualOwner(oid,pid) Values(200,2);

-- CommercialOwner Table:
Insert into CommercialOwner(oid,contactId) Values(300,3);
Insert into CommercialOwner(oid,contactId) Values(400,4);
Insert into CommercialOwner(oid,contactId) Values(500,5);

-- Pilot Table:
Insert into Pilot(pid,hireDate) Values(1,'2009-12-03');
Insert into Pilot(pid,hireDate) Values(6,'2008-01-04');
Insert into Pilot(pid,hireDate) Values(7,'2010-12-15');
Insert into Pilot(pid,hireDate) Values(8,'2011-05-06');
Insert into Pilot(pid,hireDate) Values(9,'2007-06-07');
Insert into Pilot(pid,hireDate) Values(10,'2009-03-08');
Insert into Pilot(pid,hireDate) Values(11,'2004-07-09');
Insert into Pilot(pid,hireDate) Values(12,'2005-11-10');
Insert into Pilot(pid,hireDate) Values(13,'2013-11-11');
Insert into Pilot(pid,hireDate) Values(14,'2013-11-12');

-- Hangar Table:
Insert into Hangar(hid,name,capacity) Values(1,'North',5);
Insert into Hangar(hid,name,capacity) Values(2,'West',8);
Insert into Hangar(hid,name,capacity) Values(3,'East',7);

-- License Table:
Insert into License(pid,mid,licenseDate) Values(1,5,'2009-07-01');
Insert into License(pid,mid,licenseDate) Values(6,3,'2007-04-24');
Insert into License(pid,mid,licenseDate) Values(7,1,'2009-12-25');
Insert into License(pid,mid,licenseDate) Values(8,2,'2009-10-28');
Insert into License(pid,mid,licenseDate) Values(9,3,'2004-07-17');
Insert into License(pid,mid,licenseDate) Values(10,4,'2004-12-09');
Insert into License(pid,mid,licenseDate) Values(11,5,'2001-10-13');
Insert into License(pid,mid,licenseDate) Values(12,2,'2005-04-24');
Insert into License(pid,mid,licenseDate) Values(7,3,'2009-12-26');
Insert into License(pid,mid,licenseDate) Values(8,1,'2009-10-28');
Insert into License(pid,mid,licenseDate) Values(10,3,'2004-12-11');
Insert into License(pid,mid,licenseDate) Values(11,2,'2001-10-08');
Insert into License(pid,mid,licenseDate) Values(13,6,'2013-11-12');
Insert into License(pid,mid,licenseDate) Values(14,6,'2013-11-12');

-- Airplane Table:
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(1,1,100,1,'2003-08-18');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(2,2,200,1,'2005-03-23');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(3,3,300,2,'2009-01-14');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(4,4,400,1,'2009-11-19');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(5,5,500,1,'2005-11-04');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(6,2,500,2,'2005-02-27');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(7,2,500,2,'1999-02-27');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(8,3,400,2,'1999-07-15');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(9,4,400,3,'2006-08-31');
Insert into Airplane(aid,mid,oid,hid,dateAcquired) Values(10,4,200,3,'2005-12-24');

-- Table Flight:
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(1,2,8,'2013-11-01 07:30:00','2013-11-01 07:30:00',1,'Rochester');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(2,1,7,'2013-11-01 08:30:00','2013-11-01 08:40:00',2,'Boston');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(3,3,6,'2013-11-02 07:34:00','2013-11-02 07:36:30',3,'Rochester');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(4,7,11,'2013-11-02 07:44:00','2013-11-02 07:44:00',2,'Buffalo');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(5,5,1,'2013-11-02 08:15:00','2013-11-02 08:40:00',4,'Boston');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(6,6,12,'2013-11-03 06:45:00','2013-11-03 06:40:00',1,'Buffalo');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(7,1,8,'2013-11-03 09:45:00','2013-11-03 09:46:40',1,'New York');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(8,10,10,'2013-11-04 05:30:00','2013-11-04 05:55:00',3,'Albany');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(9,6,8,'2013-11-04 07:08:00','2013-11-04 07:58:00',2,'Boston');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(10,2,11,'2013-11-04 08:05:00','2013-11-04 08:05:00',2,'New York');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(11,3,7,'2013-11-05 08:55:00','2013-11-05 08:55:00',4,'Buffalo');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(12,9,10,'2013-11-05 10:45:00','2013-11-05 12:25:00',4,'Buffalo');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(13,5,1,'2013-11-06 13:40:00','2013-11-06 00:00:00',5,'Rochester');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(14,1,8,'2013-11-07 10:00:00',NULL,2,'Rochester');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(15,10,10,'2013-11-08 08:05:00','2013-11-08 08:05:00',3,'Providence');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(16,8,7,'2013-11-08 09:13:00','2013-11-08 09:03:00',3,'Providence');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(17,7,12,'2013-11-09 07:33:00','2013-11-09 07:29:00',1,'Providence');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(18,3,6,'2013-11-09 07:40:00','2013-11-09 08:15:00',2,'Rochester');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(19,4,10,'2013-11-10 08:01:00','2013-11-10 08:00:00',2,'New York');
Insert into Flight(fid,aid,pid,plannedDeparture,actualDeparture,passengerCount,destination) Values(20,5,11,'2013-11-10 08:45:00','2013-11-10 08:35:00',5,'Buffalo');

Roman10 · Dec 3, 2013

I adjusted your query then I tried it and it showed me just one column not all the flights
this is what I tried

SELECT YEAR(plannedDeparture),
MONTH(plannedDeparture),
a.mid,
m.`name`,
COUNT(f.fid) AS `Total Flights`,
SUM(f.passengerCount) as `Total Passengers`,
AVG(f.passengerCount) AS `Average Passenger Count`
FROM airplane a
JOIN Model m
ON a.mid = m.mid
JOIN flight f
ON a.aid = f.aid
WHERE YEAR(plannedDeparture) IN (2012,2013)
GROUP BY YEAR(plannedDeparture) ASC,
MONTH(plannedDeparture) ASC

And mysql doesn't accept these in the query
a.mid ASC,
m.`name` ASC,

Roman10 · Dec 3, 2013

Finally I solved this query, thank you brother I really appreciate your help .. thank you again and again

jpadie · Dec 3, 2013

for other readers. Using your schema (slightly adjusted for my version of mysql) and your dummy data, I tried this query and got the results as shown.

Code:

SELECT      YEAR(plannedDeparture) as `Year`, 
            MONTH(plannedDeparture) as `Month`, 
            a.mid, 
            m.`name` as `Name`, 
            COUNT(f.fid) AS `Flights`, 
            SUM(f.passengerCount) as `Passengers`, 
            AVG(f.passengerCount) AS `Average Passenger Count`
FROM        Airplane a
JOIN        Model m
            ON          a.mid = m.mid
JOIN        Flight f
            ON          a.aid = f.aid
WHERE       YEAR(plannedDeparture) IN (2012,2013)
GROUP BY    YEAR(plannedDeparture) ASC, 
            MONTH(plannedDeparture) ASC,
            a.mid ASC

Code:

Year    Month   mid     Name                            Flights Passengers  Average Passenger Count
2013    11      1       Cessna Skyhawk                  3       5           1.6667
2013    11      2       Piper Archer TX                 6       9           1.5000
2013    11      3       De Havilland DHC-6 Twin Otter   4       12          3.0000
2013    11      4       Jet Prop Commander AC-695A      4       12          3.0000
2013    11      5       King Air 350, B300CER           3       14          4.6667

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

problem with getting result with my query!!!

Roman10

Technical User

jpadie

Technical User

Roman10

Technical User

Roman10

Technical User

jpadie

Technical User

jpadie

Technical User

Roman10

Technical User

Roman10

Technical User

Roman10

Technical User

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor