MySQL – How to Use Multiple SUM with Multiple LEFT JOINS

MySQL

SELECT 
`members`.`id` AS `aid` , 
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `amount` ELSE 0 END) AS `deduction_amount` , 
SUM(`portion`) AS `total_portion` 
FROM `members` 
LEFT JOIN `agents_data` ON `members`.`id` = `agents_data`.`agent_id` 
LEFT JOIN `agents_deductions` ON `members`.`id` = `agents_deductions`.`agent_id` 
LEFT JOIN `agents_commission` ON `members`.`id` = `agents_commission`.`agent_id` 
GROUP BY `members`.`id` ORDER BY `members`.`id`

The total_portion results is not correct

how I use multiple SUM with multiple LEFT JOINS

UPDATE

I have 3 tables members, agents_data, agents_deductions, agents_commission

I want to view all members and calculate agents_deductions.amount and agents_commission.portion

members

agents_deductions

agents_commission

members
agents_deductions
agents_commission
I want to calculate agents_deductions.amount it should be 12000
also calculate agents_commission.portion it should be 166666.7
for the members.id = 1

UPDATE

I tried 

SELECT
(
SELECT *, 
`frist_step`.`id` AS `aid`, 
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `agents_deductions`.`amount` ELSE 0 END) AS `deduction_amount` 
FROM `members` AS `frist_step`
LEFT JOIN `agents_deductions` ON `frist_step`.`id` = `agents_deductions`.`agent_id` 
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `frist_step`.`id` ORDER BY `frist_step`.`id` 
), 
(
SELECT *, 
`step_two`.`id` AS `aid2` ,
SUM(`agents_commission`.`portion`) AS `total_portion` 
FROM `members` AS `step_two`
LEFT JOIN `agents_commission` ON `step_two`.`id` = `agents_commission`.`agent_id`
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `step_two`.`id` ORDER BY `step_two`.`id`
),
(
SELECT * FROM `frist_step` 
LEFT JOIN `step_two` ON `frist_step`.`aid` = `step_two`.`aid2` 
GROUP BY `frist_step`.`aid` ORDER BY `frist_step`.`id` 
)

the results Error Code: 1146. Table 'frist_step' doesn't exist

Best Answer

Try

SELECT members.id AS aid, 
       ad.deduction_amount, 
       ac.total_portion 
FROM members 
LEFT JOIN ( SELECT agents_deductions.agent_id, SUM(agents_deductions.amount) deduction_amount
            FROM agents_deductions
            WHERE agents_deductions.loan_status = 0
            GROUP BY agents_deductions.agent_id
          ) ad ON members.id = ad.agent_id
LEFT JOIN ( SELECT agents_commission.agent_id, SUM(agents_commission.portion) total_portion
            FROM agents_commission
            WHERE agents_commission.loan_status = 0
            GROUP BY agents_commission.agent_id
          ) ac ON members.id = ac.agent_id
ORDER BY members.id

PS. I do not understand why the agents_data table is included in the query data source. None of its field is used in a query (except join condition) and it cannot affect to the query result because of left join (except unwanted multiplying).

Related Question