SQL & PL/SQL

hi - having a major mental block this morning, pretty sure this is an easy one so just need one of you expert types to point me in the right direction..

Using the sample data below, I just need to generate a report to show userid, mon-yy, count of transactions. I need to show the full 12 months, so with a zero if there's no transactions.

I'm not sure if this is the right approach but I'm using a subquery to generate the 12 months, as obvisouly they're not in the transaction data. The fact I have to to_char against the subquery kind of set alarm bells ringing though, I think there's a better solution but as I say, mental block.

The query at the end was to show the subquery thing works, but for the life of me I can't think today how to extend this to include the userid and give me the desired result (bottom code block).

Frustrating when this should be relatively simple!?!

thanks in advance all :o)

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

create table trans (pkid number, userid number, transdate date);

insert into trans values (1, 10, sysdate-300);
insert into trans values (2, 10, sysdate-100);
insert into trans values (3, 20, sysdate-100);
insert into trans values (4, 20, sysdate-50);
insert into trans values (5, 20, sysdate-20);
commit;

select * from trans;

      PKID     USERID TRANSDATE
---------- ---------- ---------
         1         10 05-AUG-10
         2         10 21-FEB-11
         3         20 21-FEB-11
         4         20 12-APR-11
         5         20 12-MAY-11

select sub.mon, count(t.transdate) from trans t,
(select to_char(add_months(sysdate, -level),'MON-YY') as mon
from dual
connect by level <=12) sub
where sub.mon = to_char(transdate(+),'MON-YY')
group by sub.mon
order by to_date(sub.mon,'MON-YY')
/

MON    COUNT(T.TRANSDATE)
------ ------------------
JUN-10                  0
JUL-10                  0
AUG-10                  1
SEP-10                  0
OCT-10                  0
NOV-10                  0
DEC-10                  0
JAN-11                  0
FEB-11                  2
MAR-11                  0
APR-11                  1
MAY-11                  1

12 rows selected.

Desired:

10  JUN-10  0
10  JUL-10  0
10  AUG-10  1
...and so on until...
10  MAY-11  0

20  JUN-10  0
20  JUL-10  0
20  AUG-10  0
...and so on until...
20  FEB-11  1
20  MAR-11  0
20  APR-11  1
20  MAY-11  1