How to SUM two column in My SQL left join-CodePudding

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.

CodePudding user response：

Try this SELECT sum(column_name1 column_name2) as 'Total'

This line is selecting the sum of column_name1 and column_name2 from table1, and giving it an alias of 'Total'

FROM table1

This line is specifying that we want data from table1

LEFT JOIN table2

This line is joining table1 with table2 using a LEFT JOIN. This means that all data from table1 will be included, even if there is no matching data in table2

ON table1.column_name1 = table2.column_name2;

This line is specifying the conditions for the join. In this case, it is saying that column_name1 from table1 should match column_name2 from table2

SELECT sum(column_name1   column_name2) as 'Total'
FROM table1
LEFT JOIN table2
ON table1.column_name1 = table2.column_name2;

CodePudding user response：

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.