CODEWITHSUNDEEP
sqlpostgresql
Mercurial
Mercurial

Reputation: 3885

Incorrect sum from joining two table having similar columns

Given i have two tables A and B. These two tables have certain common columns say col1 and col2. Now what i am trying to get is sum from A and sum from B together. Following is the query i am using at the moment.

select 
col1 as W_NAME, 
col2 as I_NAME, 
NVL(sum(a.accepted),0) as RECEIVED_QTY,
NVL(sum(b.issue),0) as ISSUE_QTY

from TABLE1 a, TABLE2 b
where a.col1=b.col1 and a.col2=b.col2
group by col1, col2

Data is stored in tables in following format.

TABLE1

col1       col2          accepted
data1      val1          500
data1      val1          200
data2      val1          300
data2      val2          200

TABLE2

col1       col2          issue
data1      val1          100
data1      val1          50
data2      val2          100

I am trying to get the data in following format, 

col1     col2    accepted    issue
data1    val1     700        150
data2    val1     300        0
data2    val2     200        100

In my query i am either missing some rows or the sum displayed is messed up. Any suggestions. Thanks.

Upvotes: 0

Views: 42

Answers (1)

Steven
Steven

Reputation: 15283

with TABLE1_sum as (
select 
col1 as W_NAME, 
col2 as I_NAME, 
NVL(sum(a.accepted),0) as RECEIVED_QTY
from TABLE1 a
group by W_NAME, I_NAME
),
TABLE2_sum as (
select 
col1 as W_NAME, 
col2 as I_NAME, 
NVL(sum(b.issue),0) as ISSUE_QTY
from TABLE2 b
group by W_NAME, I_NAME
)
select coalesce(a.W_NAME,b.W_NAME) as W_NAME,
       coalesce(a.I_NAME,b.I_NAME) as I_NAME,
       coalesce(RECEIVED_QTY,0) as RECEIVED_QTY,
       coalesce(ISSUE_QTY,0) as ISSUE_QTY
from TABLE1_sum
full outer join TABLE2_sum on a.W_NAME = b.W_NAME and a.I_NAME = b.I_NAME

Upvotes: 1

Related Questions