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

nbsj67_at_yahoo.com (Nimesh) wrote in message news:<ec54ea9f.0402111807.477c1edd_at_posting.google.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 Received on Thu Feb 12 2004 - 09:17:52 CET