title: Aggregate Query depending on several tables
body: <p>I want to create a database with information of employees, their jobs, salaries and projects</p>
  
  <p>I want to keep information of the cost of a project (real value of project and the days a employee invested) </p>
  
  <p>For employee and project each Employee has one role on the Project through the PK constraint, and allows for the addition of a new role type ("Tertiary" perhaps) in the future.</p>
  
  <pre><code>    CREATE TABLE Employee(
        EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
        Name        VARCHAR(30)  NOT NULL,
        Sex         CHAR(1)      NOT NULL,
        Address     VARCHAR(80)  NOT NULL,
        Security    VARCHAR(15)  NOT NULL,
        DeptID      INTEGER      NOT NULL,        
        JobID       INTEGER      NOT NULL
      );
  
      CREATE TABLE Departments  (
          DeptID   INTEGER     NOT NULL PRIMARY KEY,
          DeptName VARCHAR(30) NOT NULL
      );
  
      CREATE TABLE Jobs (
          JobID            INTEGER      NOT NULL PRIMARY KEY,
          JobName          VARCHAR(30)  NOT NULL,
          JobSalary        DOUBLE(15,3) NOT NULL default '0.000', 
          JobSalaryperDay  DOUBLE(15,3) NOT NULL default '0.000', 
          DeptID           INTEGER      NOT NULL
      );
  
  
      CREATE TABLE Project(
        ProjectID    INTEGER NOT NULL PRIMARY KEY,
        ProjectDesc   VARCHAR(200) NOT NULL,
        StartDate     DATE NOT NULL,
        EndDate       DATE NOT NULL, 
        DaysOfWork    INTEGER NOT NULL,
        NoEmployees   INTEGER NOT NULL,
        EstimatedCost DOUBLE(15,3) NOT NULL default '0.000', 
        RealCost      DOUBLE(15,3) NOT NULL default '0.000' 
      );
  
  
      CREATE TABLE `Project-Employee`(
        ProjectID    INTEGER NOT NULL,
        EmployeeID   INTEGER NOT NULL,
        Note         VARCHAR(200),
        DaysWork     INTEGER NOT NULL,
        CONSTRAINT fk_ProjectID  FOREIGN KEY (ProjectID)  REFERENCES Project(ProjectID),
        CONSTRAINT fk_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
      );
  
  
  INSERT INTO `Departments` VALUES (1, 'Outsourcing');
  INSERT INTO `Departments` VALUES (2, 'Technician');
  INSERT INTO `Departments` VALUES (3, 'Administrative');
  
  INSERT INTO `Jobs` VALUES (1, 'welder'    ,500.550,16.7 ,2);
  INSERT INTO `Jobs` VALUES (2, 'turner'    ,500.100,16.67,2);
  INSERT INTO `Jobs` VALUES (3, 'assistant' ,650.100,21.67,2);
  INSERT INTO `Jobs` VALUES (4, 'supervisor',800.909,26.70,3);
  INSERT INTO `Jobs` VALUES (5, 'manager'   ,920.345,30.68,3);
  INSERT INTO `Jobs` VALUES (6, 'counter'   ,520.324,17.35,1);
  
  
  
  INSERT INTO `Employee` VALUES (10, 'Joe',  'M', 'Anywhere', '927318344', 1, 3);
  INSERT INTO `Employee` VALUES (20, 'Moe',  'M', 'Anywhere', '827318322', 2, 3);
  INSERT INTO `Employee` VALUES (30, 'Jack', 'M', 'Anywhere', '927418343', 3, 4);
  INSERT INTO `Employee` VALUES (40, 'Marge','F', 'Evererre', '127347645', 1, 6);
  INSERT INTO `Employee` VALUES (50, 'Greg' ,'M', 'Portland', '134547633', 3, 5);
  
  
  INSERT INTO `Project` VALUES (1, 'The very first', '2008-7-04' , '2008-7-24' , 20, 5, 3000.50, 2500.00);
  INSERT INTO `Project` VALUES (2, 'Second one pro', '2008-8-01' , '2008-8-30' , 30, 5, 6000.40, 6100.40);
  
  
  INSERT INTO `Project-Employee` VALUES (1, 10, 'Worked all days'    , 20);
  INSERT INTO `Project-Employee` VALUES (1, 20, 'Worked just in defs', 11);
  INSERT INTO `Project-Employee` VALUES (1, 30, 'Worked just in defs', 17);
  INSERT INTO `Project-Employee` VALUES (1, 40, 'Contability '       , 8);
  INSERT INTO `Project-Employee` VALUES (1, 50, 'Managed the project', 8);
  </code></pre>
  
  <p>So to get the total amount of the cost of a project and have it for future Work quote I would just sum the working days of each job for each employee in an aggregate query. </p>
  
  <p>What would be the query to sum all working days knowing the employees involved in a particular project to know the cost generated for their work, <strong>Is it possible to know this with this design?</strong></p>
  
  <p>So lets suppose I know that in project 1, 5 employees were involved, and I know by other table "jobs" the salary I would pay each one of them per day</p>
  
  <p>I am doing some queries <a href="http://sqlfiddle.com/#!2/4744e" rel="nofollow">here with sqlfiddle</a></p>
  
  <p><strong>UPDATE</strong></p>
  
  <pre><code> CREATE TABLE `Sexes` (
     Sex char(1) primary key
   );
   INSERT INTO Sexes values ('M');
   INSERT INTO Sexes values ('F');
  
  CREATE TABLE `Employee`(
    EmployeeID  INTEGER      NOT NULL PRIMARY KEY,
    Name        VARCHAR(130)  NOT NULL,
    Sex         CHAR(1)      NOT NULL,
    Address     VARCHAR(380)  NOT NULL,
    Security    VARCHAR(15)  NOT NULL,
    FOREIGN KEY (Sex) references Sexes (Sex),
    CONSTRAINT `uc_EmployeeInfo` UNIQUE (`EmployeeID`,`Name`,`Security`)           
  );
  
  CREATE TABLE `Department`  (
    DeptID   INTEGER     NOT NULL PRIMARY KEY,
    DeptName VARCHAR(30) NOT NULL,
    CONSTRAINT `uc_DeptName` UNIQUE (`DeptID`,`DeptName`)
  );
  
  CREATE TABLE `Dept-Employee`(
    EmployeeID   INTEGER NOT NULL,          
    DeptID       INTEGER NOT NULL,     
    CONSTRAINT fk_DeptID     FOREIGN KEY (DeptID)     REFERENCES `Department`(DeptID),
    CONSTRAINT fk_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES `Employee`(EmployeeID)
  );
  
   CREATE TABLE `Dept-Manager`(
    EmployeeID   INTEGER NOT NULL,          
    DeptID       INTEGER NOT NULL,
    CONSTRAINT fk_DeptIDs     FOREIGN KEY (DeptID)     REFERENCES `Department`(DeptID),
    CONSTRAINT fk_EmployeeIDs FOREIGN KEY (EmployeeID) REFERENCES `Employee`(EmployeeID)
  );
  
  CREATE TABLE `Jobs` (
    JobID            INTEGER      NOT NULL PRIMARY KEY,
    JobName          VARCHAR(30)  NOT NULL,
    JobSalary        DECIMAL(7,3) NOT NULL default '0000.000', 
    JobSalaryperDay  DECIMAL(7,3) NOT NULL default '0000.000', 
    CONSTRAINT `uc_jobs` UNIQUE (`JobID`,`JobName`)
  );
  
  CREATE TABLE `Jobs-Employee`(
    EmployeeID   INTEGER NOT NULL,
    JobID        INTEGER NOT NULL,
    CONSTRAINT fk_JobIDs       FOREIGN KEY (JobID)      REFERENCES `Jobs`(JobID),
    CONSTRAINT fk_EmployeeIDss FOREIGN KEY (EmployeeID) REFERENCES `Employee`(EmployeeID)
  );
  
  CREATE TABLE `Project`(
    ProjectID     INTEGER NOT NULL PRIMARY KEY,
    ProjectName   VARCHAR(200) NOT NULL,
    StartDate     DATE    NOT NULL,
    DaysOfWork    INTEGER NOT NULL,
    NoEmployees   INTEGER NOT NULL,
    EstimatedCost DECIMAL(9,3) NOT NULL default '000000.000', 
    RealCost      DECIMAL(9,3) NOT NULL default '000000.000', 
    CONSTRAINT `uc_project` UNIQUE (`ProjectID`,`ProjectName`)           
  );
  
  CREATE TABLE `Project-Employee`(
    ProjectID    INTEGER NOT NULL,
    EmployeeID   INTEGER NOT NULL,
    Note         VARCHAR(200),
    DaysWork     INTEGER NOT NULL,
    CONSTRAINT fk_ProjectIDsss  FOREIGN KEY (ProjectID)  REFERENCES `Project`(ProjectID),
    CONSTRAINT fk_EmployeeIDsss FOREIGN KEY (EmployeeID) REFERENCES `Employee`(EmployeeID)
  );
  
  
      INSERT INTO `Department` VALUES (1, 'Outsourcing');
      INSERT INTO `Department` VALUES (2, 'Technician');
      INSERT INTO `Department` VALUES (3, 'Administrative');
  
      INSERT INTO `Jobs` VALUES (1, 'welder'    ,500.550, 16.7 );
      INSERT INTO `Jobs` VALUES (2, 'turner'    ,500.100, 16.67);
      INSERT INTO `Jobs` VALUES (3, 'assistant' ,650.100, 21.67);
      INSERT INTO `Jobs` VALUES (4, 'supervisor',800.909, 26.70);
      INSERT INTO `Jobs` VALUES (5, 'manager'   ,920.345, 30.68);
      INSERT INTO `Jobs` VALUES (6, 'counter'   ,520.324, 17.35);
  
      INSERT INTO `Employee` VALUES (10, 'Joe',  'M', 'Joewhere', '927318344');
      INSERT INTO `Employee` VALUES (20, 'Moe',  'M', 'Moewhere', '827318322');
      INSERT INTO `Employee` VALUES (30, 'Jack', 'M', 'Jaswhere', '927418343');
      INSERT INTO `Employee` VALUES (40, 'Marge','F', 'Evererre', '127347645');
      INSERT INTO `Employee` VALUES (50, 'Greg' ,'M', 'Portland', '134547633');
  
      INSERT INTO `Dept-Employee`  VALUES (10,1);
      INSERT INTO `Dept-Employee`  VALUES (20,2);
      INSERT INTO `Dept-Employee`  VALUES (30,3);
      INSERT INTO `Dept-Employee`  VALUES (40,1);
      INSERT INTO `Dept-Employee`  VALUES (50,3);
  
      INSERT INTO `Jobs-Employee`  VALUES (10,3);
      INSERT INTO `Jobs-Employee`  VALUES (20,3);
      INSERT INTO `Jobs-Employee`  VALUES (30,4);
      INSERT INTO `Jobs-Employee`  VALUES (40,6);
      INSERT INTO `Jobs-Employee`  VALUES (50,5);
  
      INSERT INTO `Project` VALUES (1, 'The very first', '2008-7-04' , 20, 5, 3000.50, 2500.00);
      INSERT INTO `Project` VALUES (2, 'Second one pro', '2008-8-01' , 30, 5, 6000.40, 6100.40);
  
      INSERT INTO `Project-Employee` VALUES (1, 10, 'Worked all days'    , 20);
      INSERT INTO `Project-Employee` VALUES (1, 20, 'Worked just in defs', 11);
      INSERT INTO `Project-Employee` VALUES (1, 30, 'Worked just in defs', 17);
      INSERT INTO `Project-Employee` VALUES (1, 40, 'Contability '       , 8);
      INSERT INTO `Project-Employee` VALUES (1, 50, 'Managed the project', 8);
  </code></pre>
  
  <p>To the new structure I did this </p>
  
  <pre><code>CREATE VIEW `Emp-Job` as
  SELECT e.*,j.jobID  
  FROM  Employee e,`Jobs-Employee` j
  WHERE e.EmployeeID = j.EmployeeID;
  
  
  
  CREATE VIEW `employee_pay` as
  select e.*, j.jobname, j.jobsalary, j.jobsalaryperday
  from `Emp-Job` e
  inner join `Jobs` j
          on e.JobID = j.JobID;
  
  create view project_pay as 
  select pe.projectid, pe.employeeid, pe.dayswork,
         e.jobsalaryperday, (e.jobsalaryperday * dayswork) as total_salary
  from `Project-Employee` pe
  inner join `employee_pay` e
          on e.employeeid = pe.employeeid
  </code></pre>