Duplicates

[rboscia]

I am a newbie and I see several posts for dups but none that seem to work for my tables.
I have a table that contains customer telephone numbers (the telephone number is in 2 separate fields)and also a customer ticket #. I want to retrieve all the duplicates of those telephone numbers by ticket #.
My output should look like this

Telephone # Ticket #
xxx-xxx-xxxx 1
8
10
Total # of Occurances ##

Telephone # Ticket #
xxx-xxx-xxxx 3
7
15
Total # of Occurances ##

I can get the dups if I use just one field in my select statement, but when I seem to select and count more than just one field, it doesn't give me any output. I sure could use some help. Thanks!

 

[Triad]

Try the following (albeit, performance may be slow):

SELECT ticket#, tel#
FROM table
WHERE tel# IN
(SELECT tel#
FROM table
GROUP BY tel#
HAVING count(*) > 1)
ORDER BY ticket#

If tel# in two fields, use tel#1 || tel#2 in place of all tel# fields above.