Getting duplicate of items in a database column

Question

Database Name = MyDB

Table Name = MyTable

Column Name = ColumnSurname

Using SQL Server 2005

I have multiple entries in ColumnSurname and some of them are spelled exactly the same. How can i return all the distinct values with the same ColumnSurname value. Meaning i want to return "Bond" if "Bond" comes up more than twice.

How would i do this in a SQL statement?

Answer 1

SELECT ColumnSurname FROM MyTable GROUP BY ColumnSurname HAVING COUNT(ColumnSurname) > 1 ?

Answer 2

SELECT ColumnSurname FROM MyTable GROUP BY ColumnSurname HAVING count(*)>1

Answer 3

select ColumnSurName, count(*) from MyTable group by ColumnSurName having count(*) >1

If you want to see how many duplicates you have