quote:

---

Originally posted by Serious Sam

I'm having trouble getting a SELECT statement to give me the results I need. Here's a sample of my table:

Table: Request
ID | Ticket_Num | fName | lName ...
1 555555
2 777777
3 777777

I need to be able to return the NUMBER (COUNT) of rows that have duplicate ticket numbers (NOT the actual ticket_num values). I just need the query to return 1 result for each row that has other duplicate rows, and not the total numebr of duplicates. So in the case above it would return a value of 1 because there is one row that has duplicates. I hope that makes sense. Here's my query right now:

SELECT COUNT(Ticket_Num)
FROM Request
GROUP BY Ticket_NUM
HAVING (COUNT(Ticket_Num) > 1)

I thought this would do it, but it returns the actual ticket value that has duplicates (777777) and not the count. Thanks in advance.

---

SELECT t.ID,t.Ticket_Num
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Ticket_Num ORDER BY ID) AS RowNo,
*
FROM Request
)t
WHERE t.RowNo >1

quote:

---

Originally posted by Serious Sam

Thanks for the replies, but neither of those worked...Visakh - I've tried yours and it returns a value of 2 with table shown in the example. It is returning all the duplicate entries. I only want a count of 1 per set of duplicate rows.

---

SELECT COUNT(*)
FROM
(
SELECT Ticket_Num
FROM Request
GROUP BY Ticket_Num
HAVING COUNT(*) >1
)t

quote:

---

SELECT COUNT(*)
FROM
(
SELECT COUNT(*) AS DupCount
FROM @table1
GROUP BY Ticket_Num
HAVING COUNT(*) > 1
)

---