Question 112746 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to SUM two column in My SQL left join

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search