Joining on the same table to get 2 different amount values

I want to get the total amount of a funding source (dim_6) against 2 different account codes (1301 AND 1300).

Below is the query that I designed.

SELECT t.dim_6,sum(t.amount) as bvalue, sum(t1.amount) as b1value   
FROM agltransact t
LEFT OUTER JOIN agltransact t1  ON t.dim_6 =t1.dim_6 
WHERE
t.dim_6 ='AWD-000261' and t1.dim_6='AWD-000261'  and (t.period between '201801' and '202102') and (t1.period between '201801' and '202102')
and t.account = '1300' and t.voucher_type!='YE' 
and t1.account = '1301' and t1.voucher_type!='YE' 

The above query doesn’t return the correct sum of amount and it doesn’t match the value of amount if I try to run a simple SQL query against 1 account code, as below

 select dim_6,sum(amount) from agltransact where dim_6= 'AWD-000261' and voucher_type!='YE' and period between '201801' and '202102' and account ='1300' group by dim_6

Both the above queries have same conditions but the amount value doesn’t matches if I JOIN.

What am I missing?

Answer

I want to get the total amount of a funding source (dim_6) against 2 different account codes (1301 AND 1300).

Just use conditional aggregation:

Below is the query that I designed.

SELECT t.dim_6,
       SUM(CASE WHEN t.account = '1300' THEN t.amount END) as value_1300,
       SUM(CASE WHEN t.account = '1301' THEN t.amount END) as value_1301
FROM agltransact t
WHERE t.dim_6 = 'AWD-000261' AND
      (t.period between '201801' and '202102') AND
      t.voucher_type <> 'YE' AND
      t.account IN ('1300', '1301')
GROUP BY dim_6;

Note: I’m providing an answer because I think this is a much better solution than the other answer.