You can use GROUP BY to display a list of duplicate records by counting the consolidated records. For example, using the Employee table:
SELECT LastName, COUNT(*) FROM Employee GROUP BY LastName HAVING COUNT(*) > 1;
As only the name "Smith" is duplicated in LastName, the results table looks like this:
LastName |
COUNT(*) |
Smith |
2 |
As you can group on several fields, you can construct more complex de-duplication queries. Using the EmployeeSales table as an example, the following query picks out the first and last occurrence of duplicated sales region / employee combinations. It also calculates a count of how many duplicates occur.
SELECT Employee, Region, COUNT(*) AS Total, FIRST(SalesID) AS [First Match], LAST(SalesID) AS [Last Match] FROM EmployeeSales GROUP BY Employee, Region HAVING COUNT(*) > 1;
This produces the following table:
Employee |
Region |
Total |
First Match |
Last Match |
John |
North |
2 |
1 |
2 |
John |
South |
2 |
5 |
8 |
Richard |
North |
3 |
4 |
7 |
Susan |
South |
2 |
3 |
9 |
See also:
Groups and aggregate functions