Select inner join returns wrong result

Question

I'm using select inner join as below. How do I get my expected result?

Here is the sql syntax:

select tgr.id, tgr.venid, sum(tgrd.qty*tgrd.pri), sum(tgrp.amo)
from tgr inner join tgrd on tgr.id = tgrd.id
inner join tgrp on tgr.id = tgrp.id
where tgr.id = 3
group by tgr.id, tgr.venid
having sum(tgrd.qty*tgrd.pri)-sum(tgrp.amo)>0;

result:

3 | 1 | 462000 | 262000

but I'm expecting result:

3 | 1 | 231000 | 131000

source from 3 tables: tgr, tgrd, tgrp

tgr table
id  venid
3   1

tgrd table
id  plu qty pri
3   2   2.7 45000
3   1   7.3 15000

tgrp table
id  type  amo
3   2     0
3   2     131000

Any help will be greatly appreciated.

Answer 1

Because you have a many-to-many relationship from tgr to tgrd and tgrp, you need to perform aggregation before JOINing the tables, otherwise you can double (or more) count each value. This query will give you the results you want:

select tgr.id, tgr.venid, total, amo
from tgr 
inner join (select id, sum(qty*pri) as total
            from tgrd
            group by id) tgrd on tgr.id = tgrd.id
inner join (select id, sum(amo) as amo
            from tgrp
            group by id) tgrp on tgr.id = tgrp.id
where tgr.id = 3
group by tgr.id, tgr.venid
having total - amo > 0;

Output:

id  venid   total       amo
3   1       231000.00   131000

Demo on 3v4l.org