# How to Find Duplicate Values in a SQL Table?

By Bobby Iliev, 2021-11-19. https://bobbyiliev.com/post/how-to-find-duplicate-values-in-a-sql-table/

# Introduction

In this quick tutorial, you will learn how to find the duplicate values in a SQL table with a single SQL query.

Let's say that we have a table called `users` with the following data:

```
SELECT username,email FROM users;
+----------+---------------------+
| username | email               |
+----------+---------------------+
| bobby    | bobby@devdojo.com   |
| devdojo  | devdojo@devdojo.com |
| tony     | tony@devdojo.com    |
| greisi   | greisi@devdojo.com  |
| bobby    | bobby@devdojo.com   |
| tony     | tony@devdojo        |
| tony     | tony@devdojo        |
| greisi   | greisi@devdojo      |
+----------+---------------------+
```

As you can see, there are multiple users with the same username and email. The result that we want to get is a list of all duplicate records ordered by the count:

```
| tony     | tony@devdojo.com   |        3 |
| bobby    | bobby@devdojo.com  |        2 |
| greisi   | greisi@devdojo.com |        2 |
```

Let's see how to do that!

# Finding Duplicate Values in a SQL Table

In order to get the duplicate values, you can group by the two specific columns. In the example above those would be the `username` and the `email` columsn.

```
SELECT
    username, email, COUNT(*)
FROM
    users
GROUP BY
    username, email
HAVING 
    COUNT(*) > 1
ORDER BY count(*)
DESC;
```

The result of the above query would be:

```
 username |       email        | count 
----------+--------------------+-------
 tony     | tony@devdojo.com   |     3
 bobby    | bobby@devdojo.com  |     2
 greisi   | greisi@devdojo.com |     2
```

## MySQL incompatible with `sql_mode=only_full_group_by`

If you are using MySQL you would get the following warning:

```
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'demo_db.users.username'; this is incompatible with sql_mode=only_full_group_by
```

To work around this you can change the `sql_mode` for the current session:

```
SET sql_mode = '' 
```

Or if you want to permanently change this, you need to update the MySQL configuration file and set `sql_mode` under the `[mysqld]` section to:

```
[mysqld]  
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
```

After that you would have to restart the MySQL service.

Once this is done, the above query should work as expected and the output would be:

```
+----------+--------------------+----------+
| username | email              | COUNT(*) |
+----------+--------------------+----------+
| tony     | tony@devdojo.com   |        3 |
| bobby    | bobby@devdojo.com  |        2 |
| greisi   | greisi@devdojo.com |        2 |
+----------+--------------------+----------+
```

# Conclusion

This is pretty much it! Now you know how to find duplicate values in a SQL table!

In case that you are just getting started with SQL, I would suggest making sure to check out this free eBook here:

[💡 Introduction to SQL eBook](https://github.com/bobbyiliev/introduction-to-sql)

In case that you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out [Materialize](https://materialize.com/)!

![Materialize - a streaming database](https://imgur.com/52d9a6h.png)

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.