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

Mysql left join and sum of one column issue

I have 2 tables:

Table tms_bills

id    load_id     company_name     
1       11          company1         
2       11          company1         
3       11          company2         
4       12          company3

Table tms_payments

id       amount       payment_associate_id
11       100                    2
12       200                    2
13       200                    3
14       50                     6

I want rows from tms_bills where load_id=11 and company_name = ‘company1’ and sum of ‘amount’ column in tms_payments where id = payment_associate_id

SELECT tb.*, SUM(amount) AS paid_amount
  FROM tms_payments tp
  LEFT JOIN tms_bills tb
    ON tb.id = tp.payment_associate_id
 WHERE tb.load_id = 11
   AND tb.company_name = 'company1'
 ORDER BY tb.id DESC;

Above code is fetching wrong result.

Output should be:

id    load_id     company_name     amount
1       11          company1         0
2       11          company1         300

Anybody can do this for me?

Tags:

2

Answers


  1. 0

    Try following query.

    SELECT tb.*, SUM(IFNULL(amount,0)) AS paid_amount
FROM tms_bills tb
LEFT JOIN tms_payments tp ON tb.id = tp.payment_associate_id
WHERE tb.load_id = 11 AND tb.company_name = 'company1'
GROUP BY tb.id
ORDER BY tb.id ASC;
    Login or Signup to reply.
  2. 0

    SELECT b.id,b.load_id,b.company_name,COALESCE(SUM(p.amount),0) as amount FROM tms_bills b LEFT JOIN tms_payments p ON b.load_id = p.payment_associate_id GROUP BY b.id,b.load_id,b.company_name ORDER BY b.id;

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

Back To Top
Search