Struggling to join multiple tables correctly to receive correct data

The situation:

I have MySQL 5. I am trying to produce a report of companies and some profit details.

• Each company has jobs that we do for them.
• Each job has piec开发者_运维知识库es of work involved in that job.
• Each piece of work has a task type

What I want to retrieve:

• I want to know the total profit for each company from all jobs
• The total costs for each company from all jobs
• A total amount of time spent on all jobs for each company - separated by the work type

companies table:

• id
• name

jobs table:

• id
• company_id
• budget
• costs

work table:

• id
• job_id
• type_id
• start_time
• end_time

types table:

• id
• name
• hourly_rate

Relationships:

companies ---< jobs ---< work >--- types

Thanks!

---

The first two can be found with this query:

select
  c.name as company_name,
  sum(hour(end_time) - hour(start_time)) * hourly_rate - sum(costs) as total_profit,
  sum(costs) as total_costs
from companies c
join jobs j on j.company_id = c.id
join work w on w.job_id = j.id
join types t on t.id = w.type_id
group by 1;

the last can be found with this query:

select
  c.name as company_name,
  t.name as work_type,
  sum(hour(end_time) - hour(start_time)) as total_hours
from companies c
join jobs j on j.company_id = c.id
join work w on w.job_id = j.id
join types t on t.id = w.type_id
group by 1, 2;