INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

problem with getting result with my query!!!

problem with getting result with my query!!!

(OP)
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);

RE: problem with getting result with my query!!!

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 

RE: problem with getting result with my query!!!

(OP)
Hi jpadie

I tried your solution, but it doesn't work

RE: problem with getting result with my query!!!

(OP)
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');

RE: problem with getting result with my query!!!

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!

RE: problem with getting result with my query!!!

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?

RE: problem with getting result with my query!!!

(OP)
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');

RE: problem with getting result with my query!!!

(OP)
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,

RE: problem with getting result with my query!!!

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

RE: problem with getting result with my query!!!

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 --> results

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close