0
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

4
  • "not correct" is quite vague - please describe it more completely. I don't see why you are using LEFT JOINs., a [INNER] JOIN would seems more appropriate and save you trying to SUM over null values. What table(s) are amount and portion from?
    – danblack
    Sep 10, 2018 at 23:25
  • Remove the GROUP BY from the and change SUM( {fields} ..) to just the fields and see if that query shows the same rows you have above.
    – danblack
    Sep 11, 2018 at 3:35
  • 1) Specify table alias for EACH field in the query text. 2) Calculate sums you need in subqueries before joining. Summation after joining will multiply the sum by the amount of the corresponding records in the other tables.
    – Akina
    Sep 11, 2018 at 4:32
  • @Akina sorry can you write the statement as i cannot figure out it
    – M.M
    Sep 11, 2018 at 10:59

1 Answer 1

1

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).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.