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!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem with getting result with my query!!!

Status
Not open for further replies.
Roman10

Roman10

Technical User
Joined
Dec 2, 2013
Messages
6
Location
US
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);
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Hi jpadie

I tried your solution, but it doesn't work
 
Upvote 0 Downvote
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');
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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');

 
Upvote 0 Downvote
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,
 
Upvote 0 Downvote
Finally I solved this query, thank you brother I really appreciate your help .. thank you again and again
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question Question
filtering data with vfp 8
Replies
0
Views
308
bharons
bharons
lupidol
  • Locked
  • Question Question
mysql cannot add rows with &quot;UNION&quot;
Replies
5
Views
973
Chris Miller
Chris Miller
boylesg
  • Locked
  • Question Question
Can't sort my table by a particular column, records are returned in autinc order.
Replies
4
Views
881
spamjim
spamjim
dylim
  • Locked
  • Question Question
Why MySQL 5.7 is still getting updates
Replies
3
Views
818
spamjim
spamjim
E
  • Locked
  • Question Question
MySQL calculator tools
Replies
0
Views
656
EdZzy
E

Part and Inventory Search

Sponsor

Back
Top