By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,333 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,333 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 11 Feb 2004, nb****@yahoo.com 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
Jul 19 '05 #2

P: n/a
nb****@yahoo.com (Nimesh) wrote in message news:<ec**************************@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
Jul 19 '05 #3

P: n/a
nb****@yahoo.com (Nimesh) wrote in message news:<ec**************************@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.


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
Jul 19 '05 #4

P: n/a
nb****@yahoo.com (Nimesh) wrote in message news:<ec**************************@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.


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
Jul 19 '05 #5

P: n/a
nb****@yahoo.com (Nimesh) wrote in message news:<ec**************************@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.


Use COUNT(*), GROUP BY and HAVING.
Jul 19 '05 #6

P: n/a
SELECT customer_name, Count(*) num_occurances
FROM dtb_customer
group by customer_name
having count(*) > 1

will do it..
nb****@yahoo.com (Nimesh) wrote in message news:<ec**************************@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.

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.