Select Statement producing duplicate rows?

From: <newopt_at_my-deja.com>
Date: 2000/06/16
Message-ID: <8ie1ee$88j$1@nnrp1.deja.com>#1/1

The query below select on 2 tables a customer master table (CUMSTPF) and a services table(SRVRPF) I used the GROUP BY to eliminate duplicates but my counts are doubled whe a customer has records for both service codes in the "S.AOSVCD In ('A001','P001')" statement below. How can I get it to count the customer only once if that customer has records for both service codes?

Thanks in advance!

SELECT C.CUNROV, C.CUCNO, C.CUSTA

FROM  NE_ICOMS.SRVRPF       S,
            NE_ICOMS.CUMSTPF      C
WHERE C.CUCNO = S.AOCNBR
AND   C.CUNROV = S.AONROV
AND   C.CUSTA = 'A'
AND   S.AOSVCD In ('A001','P001')

AND S.AOSYP8= 1
AND S.AOCEK6='C'
GROUP BY C.CUNROV, C.CUCNO, C.CUSTA; What I would like to do is count the number of customers once. I'm not doing a straight count, because if a customer has both of these service codes then the count would be 2 not 1.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 16 2000 - 00:00:00 CDT