Mysql sum of two fields from two different table

Question

I want to sum of two fields which are from different table acc to labour_master_id. But when I get results it shows different result.

Here is what I tried

select  labours.labour_master_id, 
    sum(labours.amount_paid), 
    sum(labour_cashcredits.amount_paid) 
FROM labours 
left join labour_cashcredits 
    on labours.labour_master_id=labour_cashcredits.labour_master_id 
group by labours.labour_master_id

It may be mapping twice so it shows wrong result.

Now problem is occurring on sum.

Any idea how to do this?

i am having error in this query..please help

select  l.labour_master_id, l.amount_paid as payable, cc.amount_paid as paid
                FROM
                (
                  select labour_master_id, sum(amount_paid) amount_paid
                  from labours 
                  group by labour_master_id
                ) l
                left join
                (
                  select labour_master_id, sum(amount_paid) amount_paid
                  from labour_cashcredits 
                  group by labour_master_id
                ) cc
                                left join
                (
                  select name,id
                  from labour_masters
                ) lm
                    on l.labour_master_id=cc.labour_master_id 
on l.labour_master_id=lm.labour_masters.id

why are you using left join here? can you please give data sample? — Ajith Sasidharan
– Ajith Sasidharan, Commented Dec 19, 2012 at 11:37

Taryn · Accepted Answer · 2012-12-19 11:42:34Z

1

You can try using subqueries to perform the sum in each table:

select  l.labour_master_id, 
    l.amount_paid, 
    cc.amount_paid
FROM
(
  select labour_master_id, sum(amount_paid) amount_paid
  from labours 
  group by labour_master_id
) l
left join
(
  select labour_master_id, sum(amount_paid) amount_paid
  from labour_cashcredits 
  group by labour_master_id
) cc
    on l.labour_master_id=cc.labour_master_id

answered Dec 19, 2012 at 11:42

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amandeep Kaur Over a year ago

Thanks it works for me .. is there any method to doing without sub-queries?

Taryn Over a year ago

@AmandeepKaur You might be able to alter this to only use a subquery on the second table, but IMO it is sometimes easier to use subqueries.

Amandeep Kaur Over a year ago

i also want a labour name form labour masters table in my above query. how to join that table with this sub query???

Taryn Over a year ago

@AmandeepKaur you should be able to just join to the other table using the labour_master_id to return the name.

Nipun Jain · Accepted Answer · 2012-12-19 11:39:29Z

0

use this--

      SELECT   labours.labour_master_id, SUM (labours.amount_paid),
     SUM (labour_cashcredits.amount_paid)
  FROM labours,labour_cashcredits
     where labours.labour_master_id = labour_cashcredits.labour_master_id
 GROUP BY labours.labour_master_id

answered Dec 19, 2012 at 11:39

Nipun Jain

6014 silver badges6 bronze badges

Comments

Mariappan Subramanian · Accepted Answer · 2012-12-19 11:42:50Z

0

Hope this is what you want,

select  labours.labour_master_id, 
    sum(ifnull(labours.amount_paid,0))+
    sum(ifnull(labour_cashcredits.amount_paid),0) sum
FROM labours 
left join labour_cashcredits 
    on labours.labour_master_id=labour_cashcredits.labour_master_id 
group by labours.labour_master_id

answered Dec 19, 2012 at 11:42

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

Comments

Sithu · Accepted Answer · 2012-12-19 11:43:13Z

0

Try using sub-query for the second SUM:

SELECT  labours.labour_master_id, SUM(labours.amount_paid), 
          ( SELECT SUM(amount_paid) FROM labour_cashcredits 
            WHERE labour_master_id = labours.labour_master_id 
            GROUP BY labour_master_id
           ) AS credit_amount_paid
FROM labours
LEFT JOIN labour_cashcredits ON labours.labour_master_id = labour_cashcredits.labour_master_id 
GROUP BY labours.labour_master_id

answered Dec 19, 2012 at 11:43

Sithu

4,88210 gold badges68 silver badges113 bronze badges

Collectives™ on Stack Overflow

Mysql sum of two fields from two different table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related