Find duplicate fields in SQL

Let's say that you have a table named users, where you keep your application users. If you has forgotten to set the username field as unique and you have some duplicate fields, you can use the following query:

SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1