Count Duplicates in SQL

As a database administrator, you will come across instances where you need to determine the duplicate values inside a given table column. This is especially useful when you need to cleanup the table that contain distinct values.

There is a wide array of instances where duplicates can occur in a given table. This can include automated imports and lack of constraints in the table. Luckily, we have various tools and techniques of determining the duplicate values in a table column.

In this tutorial, we will explore the various methods and techniques that we can use to get the number of duplicate values inside a table column.

Sample Data

Before diving into the various methods and techniques, let us setup a basic table with sample data that can allow us to demonstrate the techniques of counting the duplicates in a table.

For our case, we create a basic table that can store the network information as shown in the following query:

CREATE TABLE network_info (
id INT PRIMARY KEY,
hostname VARCHAR(255),
ip_address VARCHAR(15)
);
INSERT INTO network_info (id, hostname, ip_address)
VALUES
(1, 'server1', '192.168.1.1'),
(2, 'server2', '192.168.1.2'),
(3, 'server3', '192.168.1.3'),
(4, 'server4', '192.168.1.4'),
(5, 'server1', '192.168.1.1'),
(6, 'server6', '192.168.1.6'),
(7, 'server7', '192.168.1.7');

In this case, we store the hostname and associated IP address of the various servers.

Method 1: Using the GROUP BY and HAVING Clauses

One of the methods that we can use is combining the GROUP BY and HAVING clauses. The query groups the records based on the specified columns and then filters the groups with a count greater than 1 which is essentially the duplicate values.

An example is as follows:

SELECT hostname, ip_address, COUNT(*) AS duplicate_count

FROM network_info

GROUP BY hostname, ip_address

HAVING COUNT(*) > 1;

This should return the duplicate record and the number of duplicates in the table.

An example output is as follows:

Method 2: Using the Window Function

In SQL, we also have access to the window functions such as the COUNT and OVER clause which we can use to determine the number of duplicate values of the window functions.

An example is as follows:

SELECT id, hostname, ip_address,

COUNT(*) OVER (PARTITION BY hostname, ip_address) AS duplicate_count

FROM network_info;

This technique utilizes the COUNT() function as a window function which partitions the data by the hostname and IP address. We then count the duplicates for each row. The resulting output is as follows:

id|hostname|ip_address |duplicate_count|
--+--------+-----------+---------------+
1|server1 |192.168.1.1| 2|
5|server1 |192.168.1.1| 2|
2|server2 |192.168.1.2| 1|
3|server3 |192.168.1.3| 1|
4|server4 |192.168.1.4| 1|
6|server6 |192.168.1.6| 1|
7|server7 |192.168.1.7| 1|

Method 3: Using the Common Table Expressions (CTE)

Another feature that you will find in SQL databases is the Common Table Expressions which is commonly known as CTEs.

Common Table Expressions are a fundamental feature in SQL that allows us to create temporary result sets within an SQL statement. They play a crucial role in simplifying the complex queries by breaking them into smaller subqueries.

We can use CTE to calculate the duplicate values in a table as demonstrated in the following example:

SELECT n.id, n.hostname, n.ip_address, cte.duplicate_count

FROM network_info n

JOIN DuplicateCTE cte

ON n.hostname = cte.hostname AND n.ip_address = cte.ip_address;

In this case, we create a CTE to count the duplicates and then joins it with the original table to retrieve the duplicate counts.

The resulting output is as follows:

id|hostname|ip_address |duplicate_count|
--+--------+-----------+---------------+
5|server1 |192.168.1.1| 2|
1|server1 |192.168.1.1| 2|

There you have it!

Conclusion

In this post you can see how to count how many values have duplicate or equivalent values in the result set of a query.