Question regarding left join

You Asked

Hello All,
Here's my problem:
I have a Rep table:

create table rep (id number, name varchar2(20));

insert into rep values (1,'X01');
insert into rep values (2,'X02');

And i have a Txn table

 create table txn (id number, type varchar2(20), amount number, rep_id number);
insert into txn values ('1', 'P', '4000', '1');
insert into txn values ('2', 'T', '2000', '1');
insert into txn values ('3', 'P', '700', '2');
insert into txn values ('4', 'P', '1200', '1');
insert into txn values ('5', 'T', '1300', '2'); 

I need to write a query that would return results like :
|REP|P_AMOUNT|T_AMOUNT
X01 5200 2000
X02 700 1300
I have come up with this

select r.name, p.P as p_amount, t.T as t_amount
from rep r
left join 
  ( select rep_id, sum(amount) as P from txn  where type='P' group by rep_id
  )p on p.rep_id = r.id
left join
  ( select rep_id, sum(amount) as T from txn  where type='T' group by rep_id
  )t on t.rep_id = r.id

Is it a good approach? The cost is low, but I am just not sure as to how this query would perform as compared to some other approaches.
Please advise.
Thanks in advance!

and we said...

ops$tkyte%ORA11GR2> select rep.id, rep.name,
  2         sum( case when txn.type = 'P' then amount end ) p_amount,
  3         sum( case when txn.type = 'T' then amount end ) t_amount
  4    from rep, (select * from txn where type in ('P','T')) txn
  5   where rep.id = txn.rep_id(+)
  6   group by rep.id, rep.name
  7  /

        ID NAME                             P_AMOUNT   T_AMOUNT
---------- ------------------------------ ---------- ----------
         1 X01                                  5200       2000
         2 X02                                   700       1300

would be the way I'd write it, the inline view to get only P's and T's is only necessary if there are other "types" in there (even then it is not necessary, just likely more efficient)
You can use the "left join" syntax if you prefer. I personally find it less readable.

 

---------------------------------------------------------------------------------------

When I read this question I immediately thought of the new PIVOT syntax in 11g.  I know there are 
many, many formulations of SQL to get an answer and the PIVOT formulation may not be the best 
performer but it's certainly worth consideration.

I added three rows to demonstrate; 1) the condition of no transactions for a sales representative, 
and 2) only one type of transaction for a given sales representative.

insert into rep values (3,'X03');
insert into rep values (4,'X04');
insert into txn values ('6', 'T', '6300', '3'); 

Here's the formulation with PIVOT and its results.

ORA11GR2>r
  1  SELECT name, p_amount, t_amount
  2    FROM ( SELECT rep.name, txn.type, txn.amount
  3             FROM rep, txn
  4            WHERE rep.id = txn.rep_id (+) )
  5   PIVOT ( SUM( amount) AS amount
  6     FOR ( type ) IN ( 'P' AS p, 'T' AS t ) )
  7*  ORDER BY p_amount NULLS FIRST, t_amount NULLS FIRST, name

NAME                   P_AMOUNT   T_AMOUNT
-------------------- ---------- ----------
X04
X03                                   6300
X02                         700       1300
X01                        5200       2000