How to retrieve only duplicate values from a table ?

Solution

SELECT <column> FROM <table> GROUP BY <column> HAVING COUNT(*) > 1

You can use GROUP BY to retrieve duplicate records in a table as shown in the above SQL. Let's look at the following example.

Table: Customers

CustID	Name	Address	City	Country	PostCode	Phone
CUST001	Maria Anders	Obere Str. 57	Berlin	Germany	12209	030-0074321
CUST002	Ernst Handel	Kirchgasse 6	Graz	Austria	8010	7675-3425
CUST003	Helen Bennett	Garden House Crowther Way	Cowes	UK	PO31 7PJ	(198) 555-8888
CUST004	Paula Wilson	2817 Milton Dr.	Albuquerque	USA	87110	(505) 555-5939
CUST005	Liu Wong	55 Grizzly Peak Rd.	Butte	USA	59801	(406) 555-5834

SELECT Country FROM Customers
GROUP BY
Country
HAVING COUNT(*) > 1

The above SQL statement will return the following result.

Country	Expression
USA	2