I used this to solve a problem today.
// Finding duplicates in a table
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
// Find rows that occur exactly once
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
// Return all duplicate cities and how often they appear
// Works with ORACLE
select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1
// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL
select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc
// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear
select city_name, count(city_name) as cnt
from areas
group by city_name
// version for Micrsoft’s MSSQL Server
// make use of the HAVING clause
select city_name
from areas
group by city_name
having count(*) > 1
// multiple columns in MySQL:
SELECT PL_CONTACT_FK, PL_PHONE_FK, COUNT(*) as DuplicateCount FROM PHONE_ALTERNATE GROUP BY PL_CONTACT_FK, PL_PHONE_FK HAVING DuplicateCount > 1;