How to SUM two column in My SQL left join

Question

So i have table Project

---------------------------------
project_id   |     name| 
---------------------------------
     1            | project 1  | 
     2            | project 2  | 
---------------------------------

table 02 report

----------------------------------------------------
report_id    |     report_emp       | report_hours |report_project_id|   
---------------------------------------------------
  1          |         1            |       5    |        1        | 
  2          |         2            |       7    |        1        | 
  3          |         1            |       9    |        2        | 
  4          |         2            |       6    |        1        | 
  5          |         3            |       8    |        2        | 
--------------------------------------------------

and table 03 emp

 ----------------------------------------------
emp_id       |  emp_hourly_cost   |      name  | 
-----------------------------------------------
    1        |         8.5        |       A    | 
    2        |         10         |       B    | 
    3        |         12         |       C    | 
    4        |         9          |       D    | 
    5        |         7.5        |       D    | 
-----------------------------------------------

as a final result i wanted to get this table

---------------------------------------------
     project     |    hours    |  total cost | 
---------------------------------------------
       1         |      18     |       172.5    | 
       2         |      17     |       172.5    | 
---------------------------------------------

until now i only got the project with total hours but not total cost in this statement

SELECT * from projects left join(select sum(report_hours ) as hours , daily_reports_project_id 
     from project_id group by report_id)x on x.report_project_id= projects.project_id;

total cost would be sum of all > [total hours of the project of the emp in table 2 * emp_hourly_cost in table 3 ]

i appreciation the help.

Answer 1

Join the tables and aggregate:

SELECT p.project_id project,
       SUM(r.report_hours) hours,
       SUM(r.report_hours * e.emp_hourly_cost) total_cost
FROM project p
LEFT JOIN report r ON r.report_project_id = p.project_id
LEFT JOIN emp e ON e.emp_id = r.report_emp
GROUP BY project_id;

If there are projects without reports and you want to get 0 instead of NULL as result use also COALESCE():

SELECT p.project_id project,
       COALESCE(SUM(r.report_hours), 0) hours,
       COALESCE(SUM(r.report_hours * e.emp_hourly_cost), 0) total_cost
FROM project p
LEFT JOIN report r ON r.report_project_id = p.project_id
LEFT JOIN emp e ON e.emp_id = r.report_emp
GROUP BY project_id;

See the demo.