Duplicate records when using any join-CodePudding

I have two tables. The first table has a positive value and second table has a negative value. Like this

I tested to combine two tables to result the total from SUM(positive.nett) and SUM(negative.nett) based on trx_number, and then I will combine to result grand total (SUM positive SUM negative)

SELECT p.trx_number,
       SUM(p.nett) total1,
       SUM(n.nett) total2
  FROM positif p
  FULL JOIN negatif n
    ON p.trx_number = n.trx_number
 GROUP BY p.trx_number

But the result like this

I realized that number 3 (trx_id) has a duplicate result, and the result of number 3 should be the same number between positive and negative. I tried to fix that, but it still doesn't work.

Please help me for this

CodePudding user response：

Use an inner join, and GROUP BY before you join the tables.

Example

create table positif ( trx, nett )
as 
select 3, 2147600 from dual union all
select 3, 2068300 from dual union all
select 4, 50000   from dual union all
select 5, 100000  from dual ;

create table negatif ( trx, nett )
as
select 3, -1073800 from dual union all
select 3, -1073800 from dual union all
select 3, -2068300 from dual union all
select 4, -20000   from dual union all
select 5, -100000  from dual ;

Query

select P.trx, P.sum_ totalpos, N.sum_ totalneg
from 
  ( select trx, sum( nett ) sum_ from positif group by trx ) P
  join
  ( select trx, sum( nett ) sum_ from negatif group by trx ) N
  on P.trx = N.trx 
order by 1
;

-- result
TRX TOTALPOS    TOTALNEG
3   4215900     -4215900
4   50000       -20000
5   100000      -100000

DBfiddle

The main problem with your full join is that it returns too many rows. Eg try just joining the TRX columns -> you get 2 times 3 (6) rows. What you need is: one row per TRX value. (Thus, group before you join.)

Too many rows ...

select P.trx
from positif P full join negatif N on P.trx = N.trx ;

TRX
3
3
3
3
3
3
4
5

Alternative: You could also use UNION ALL, and then GROUP BY (and sum) eg

UNION ALL

select trx, nett as pos, null as neg from positif
union all
select trx, null, nett from negatif ;

-- result
TRX POS     NEG
3   2147600 null
3   2068300 null
4   50000   null
5   100000  null
3   null    -1073800
3   null    -1073800
3   null    -2068300
4   null    -20000
5   null    -100000

GROUP BY and SUM

select trx 
, sum ( pos ) totalpos, sum( neg ) totalneg 
from (
  select trx, nett as pos, null as neg from positif
  union all
  select trx, null, nett from negatif 
) 
group by trx 
order by 1 ;

-- result
TRX TOTALPOS    TOTALNEG
3   4215900 -4215900
4   50000   -20000
5   100000  -100000