Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Select Statement producing duplicate rows?
If you're just after the number of customers, then you can use SELECT
COUNT(DISTINCT col)...
Alternatively, if you need those columns, use: SELECT C.CUNROV, C.CUCNO,
C.CUSTA ...
-- Jim Harrison Colway Software and Design www.colway.net <newopt_at_my-deja.com> wrote in message news:8ie1ee$88j$1_at_nnrp1.deja.com...Received on Sat Jun 17 2000 - 00:00:00 CDT
> 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.