Selecting group of 1 only

[dande]

We have a table that lists client enrollments. Each record may assign a 'program' and a 'reporting unit'. There may be mulitple enrollment records for a client.
I am trying to produce a list of clients that only have an active program assignment of '900' and no other active enrollments. I have used a nested select to filter clients that have an active '900' program. Then I have used a count to determine the actual number of active records against a client. Now I'm stuck. Does my logic need redirected or am I heading down the right path?

THE CODE

select count(*)as counter_n,client_c
from cd.enrollments
where clientid_c in
(select clientid_c
from cd.enrollments
where enddate_d is null and program_c = '900')
and enddate_d is null
and (ru_c = '' or ru_c is null)
group by client_c

SOME RESULTS
counter_n client_c
----------- -----------
1 00A001
1 00A009
2 00A027
3 00A055
2 00B001
1 00B004
1 00B021
1 00B026

How can I get clients with counter_n = 1 only?

Thanks, Paul

 

[smin]

Attach this line at the end of your statement.

having count(*) = 1

or without the subquery

select count(clientid_c) as counter_n, 
       clientid_c, 
from cd.enrollments
where enddate_d is null 
  and program_c = '900'
  and (ru_c = '' or ru_c is null)
group by client_c
having count(clientid_c) = 1