开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜