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
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
LEFT JOIN
s., a[INNER] JOIN
would seems more appropriate and save you trying toSUM
over null values. What table(s) areamount
andportion
from?GROUP BY
from the and changeSUM( {fields} ..)
to just the fields and see if that query shows the same rows you have above.