SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

[Nimesh]

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.

SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1


Any help would be appreciated.

Thanks in advance.

[Galen Boyer]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

On 11 Feb 2004, nbsj67@yahoo.co m wrote:

I need to find customer's names that repeat / occur more than
once in the same table. I have treid many options and I have
tried comparing the column to itself but Oracle gives me an
error.

select cust_nm, count(*)
from table
group cust_nm
having count(*) 1;

--
Galen Boyer

[Dave]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

nbsj67@yahoo.co m (Nimesh) wrote in message news:<ec54ea9f. 0402111807.477c 1edd@posting.go ogle.com>...

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.
>
SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1
>
>
Any help would be appreciated.
>
Thanks in advance.

Can you explain your SQL? How is it counting names??? You need an
aggregate query to accomplish this...

SELECT customer_name, count(*) cnt
from dtb_customer
group by customer_name

this will give you the count per customer_name

to accomplish your goal...either wrap the sql like this...

select *
from
(
SELECT customer_name, count(*) cnt
from dtb_customer
group by customer_name
)
where cnt 1

or get the same exact effect with the HAVING CLAUSE...

SELECT customer_name, count(*) cnt
from dtb_customer
group by customer_name
having count(*) 1

Dave

[sybrandb@yahoo.com]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

nbsj67@yahoo.co m (Nimesh) wrote in message news:<ec54ea9f. 0402111807.477c 1edd@posting.go ogle.com>...

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.
>
SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1
>
>
Any help would be appreciated.
>
Thanks in advance.

select a.customer_name , count(*)
from dtb_customer a
group by a.customer_name
having count(*) 1

That's all!

Sybrand Bakker
Senior Oracle DBA

[kibeha]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

nbsj67@yahoo.co m (Nimesh) wrote in message news:<ec54ea9f. 0402111807.477c 1edd@posting.go ogle.com>...

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.
>
SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1
>
>
Any help would be appreciated.
>
Thanks in advance.

Standard way of discovering duplicates would be like this :


select a.customer_name , count(*)
from dtb_customer a
group by a.customer_name
having count(*) 1



/KiBeHa

[Tony]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

nbsj67@yahoo.co m (Nimesh) wrote in message news:<ec54ea9f. 0402111807.477c 1edd@posting.go ogle.com>...

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.
>
SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1
>
>
Any help would be appreciated.
>
Thanks in advance.

Use COUNT(*), GROUP BY and HAVING.

[Kalmact]

Re: SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question

SELECT customer_name, Count(*) num_occurances
FROM dtb_customer
group by customer_name
having count(*) 1

will do it..


nbsj67@yahoo.co m (Nimesh) wrote in message news:<ec54ea9f. 0402111807.477c 1edd@posting.go ogle.com>...

I need to find customer's names that repeat / occur more than once in
the same table. I have treid many options and I have tried comparing
the column to itself but Oracle gives me an error.
>
SELECT a.customer_name , b.customer_name
FROM dtb_customer a, dtb_customer b
where a.dtb_customer = b.dtb_customer
and b.customer 1
>
>
Any help would be appreciated.
>
Thanks in advance.