Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Select Statement producing duplicate rows?

Re: Select Statement producing duplicate rows?

From: Jim Harrison <jim_at_colway.freeserve.co.uk>
Date: 2000/06/17
Message-ID: <8ifbqr$i4a$1@newsg2.svr.pol.co.uk>#1/1

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...

> 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 Sat Jun 17 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US