Query to sum different columns from different table in mysql

Question

I have three tables where i am trying to join them and get their sum from two tables and add the two sum from tables.

Here i have a SQL Fiddle for the structure of my Query: http://www.sqlfiddle.com/#!9/cb00bc/3/0

I want the output something like this:

emp_id   firstname  middlename  lastname    name_ext    SUM(amount_paid)    SUM(salary_paid) SUM(total) 

  1        test        test        test        test          300                  500             800

Answer 1

Here's your query. a subquery with the totals group by employee id will be joined by your tables.

SELECT DISTINCT tbl_user.firstname,
  tbl_user.middlename,
  tbl_user.lastname,
  tbl_user.name_ext,
  t3.amount_paid,
  t3.salary_paid,
  t3.total
FROM tbl_user
INNER JOIN tbl_salary ON tbl_user.employee_id = tbl_salary.employee_id
LEFT JOIN tbl_overtime ON tbl_overtime.employee_id = tbl_salary.employee_id
left join
  (select t1.employee_id
       , SUM(coalesce(t2.amount_paid, 0)) as amount_paid
       , SUM(coalesce(t1.salary_paid, 0)) as salary_paid
       , SUM(coalesce(t2.amount_paid, 0))  + SUM(coalesce(t1.salary_paid, 0)) as total
     from tbl_salary t1
     left join tbl_overtime t2 on t2.employee_id = t1.employee_id
     group by t1.employee_id) as t3 on t3.employee_id = tbl_user.employee_id