Newbie SQL question

From: Richard Olivier <rolivier56_at_earthlink.net>
Date: Sat, 22 Feb 2003 19:20:12 GMT
Message-ID: <M3Q5a.593$cD5.73373@newsread2.prod.itd.earthlink.net>

I have two tables, a customer table and a region table, I would like to get a count of the number
of customers in each region and I would like to display the region description

Customer table:

SETID       CUSTID       REGION
SHARE     0001
SHARE     0002
SHARE     0003             A
SHARE     0004             B

Subcust Table (Region):
SETID      REGION       DESCR         EFFDT
SHARE    A                   Region A        01-JAN-1900
SHARE    B                   Region B         01-JAN-1900

The SQL I am using is below. The result I am getting is as follows:

Count Region Descr

1          A           Region A
1          B           Region B

I would Like to see:

Count Region Descr
2

1          A           Region A
1          B           Region B

In other words, I need to see how many customers don't have a region assigned to them.

Why is this outer join not returning all the records in the customer table?

select distinct count(*),a.subcust_qual2,b.descr from ps_customer a, ps_subcust_q2_tbl b
where a.setid = b.setid (+)
and a.subcust_qual2 = b.subcust_qual2 (+) and b.effdt = (select max(effdt) from ps_subcust_q2_tbl

                    where b.setid = setid
                    and   b.subcust_qual2 = subcust_qual2
                    and   effdt <= sysdate)

group by a.subcust_qual2,b.descr
order by a.subcust_qual2 Received on Sat Feb 22 2003 - 13:20:12 CST