mysql - Select the amount of billable hours and non-billable hours -

i started in database, kind me please. application want write seems basic, tried, failed.

here problem: have this:

table: employee

# | colonne | type | interclassement | attributs | null | défaut | | action 1 | id | int(11) | no | aucune | auto_increment 2 | name | varchar(50) | latin1_swedish_ci | yes | null 3 | firstname | varchar(50) | latin1_swedish_ci | yes | null |  4 | idnumber | int(11) | yes | null 5 | mail | varchar(50) | latin1_swedish_ci | no | aucune |  6 | password | varchar(50) | latin1_swedish_ci | no | aucune |  7 | site | varchar(50) | latin1_swedish_ci | yes | null |  8 | entrance | date | yes | null |  9 | departure | date | yes | null |  10 | car_id | int(11) | yes | null |  11 | profil_id | int(11) | yes | null |  

table : imputation

# | colonne | type | interclassement | attributs | null | défaut | | action 1 | id | int(11) | | | no | aucune | auto_increment 2 | hours | int(11) | | | yes | null |  3 | description | varchar(256) | latin1_swedish_ci | | yes | null |  4 | tobebilled | tinyint(1) | | | yes | 1 |  5 | billnumber | int(11) | | | yes | null |  6 | day | date | | | yes | null |  7 | timesheet_id | int(11) | | | no | aucune |  8 | project_id | int(11) | | | no | aucune |  9 | automatic | tinyint(1) | | | no | 0 |  

table : timesheet

# | colonne | type | interclassement | attributs | null | défaut | | action 1 | id | int(11) | | | no | aucune | auto_increment 2 | month | int(2) | | | yes | null |  3 | year | int(4) | | | yes | null |  4 | filled | tinyint(1) | | | yes | 0 |  5 | closed | tinyint(1) | | | yes | 0 6 | employee_id | int(11) | | | no | aucune |  

and want achieve following result :

________________________________________________________ name     | billable hours | non-billable hours | total hours ________________________________________________________ john doe | 872            | 142                | 1014  ________________________________________________________ 

billable hours tobebilled lines = true. non-billable hours tobebilled lines = false.

here sql query i'm working on (i use flyspeed ​​sql query tool me build sql queries) :

select    employee.name,   sum( imputation.hours),    imputation.tobebilled    employee inner join    timesheet on  timesheet.employee_id =  employee.id,    imputation    imputation.tobebilled = 'true' group    employee.name,  imputation.tobebilled order    employee.name 

---

after help, here final query :

select   employee.name name,   sum(case when imputation.tobebilled = '1' imputation.hours end) `billable`,   sum(case when imputation.tobebilled = '0' imputation.hours end) `nonbillable`,   sum(imputation.hours) `total`   employee inner join   timesheet on timesheet.employee_id = employee.id inner join   imputation on imputation.timesheet_id = timesheet.id group   employee.name, employee.id order   name 

start doing proper joins between tables. never use comma in from statement. conditional aggregation -- nesting case statements in sum():

select e.name, sum( i.hours) totalhours,        sum(case when i.tobebilled = 1 i.hours else 0 end) billable,        sum(case when i.tobebilled = 0 i.hours else 0 end) nonbillable employee e inner join      timesheet ts      on ts.employee_id =  e.id inner join      imputation      on i.timesheetid = ts.timesheetid group e.name, e.id order employee.name; 

this assumes values taken tobebilled 0 , 1. inclusion of e.id in group by handle situation 2 employees have same name.