How to Find Duplicates Values Across Multiple Columns in SQL?
In SQL, sometimes we need to find duplicate entries across multiple columns in a table in a single query. We will use the GROUP BY and COUNT keywords to achieve this. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database and Select keyword.
Step 1: Create a Database. For this use the below command to create a database named w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: Use the w3wiki database. For this use the below command.
Query:
USE w3wiki
Output:
Step 3: Create a table of POSTINGS inside the database w3wiki. This table has 4 columns namely POSTING_ID, OFFICER_NAME, TEAM_SIZE and POSTING_LOCATION containing the id, the name of the officer, the size of the team and locations of the postings of Foreign Services in different countries.
Query:
CREATE TABLE POSTINGS( POSTING_ID INT, OFFICER_NAME VARCHAR(10), TEAM_SIZE INT, POSTING_LOCATION VARCHAR(10));
Output:
Step 4: Describe the structure of the table POSTINGS.
Query:
EXEC SP_COLUMNS POSTINGS;
Output:
Step 5: Insert 5 rows into the POSTINGS table.
Query:
INSERT INTO POSTINGS VALUES(1,'RYAN',10,'GERMANY'); INSERT INTO POSTINGS VALUES(2,'JACK',6,'ROMANIA'); INSERT INTO POSTINGS VALUES(3,'JANE',4,'HAWAII'); INSERT INTO POSTINGS VALUES(4,'JIM',10,'GERMANY'); INSERT INTO POSTINGS VALUES(5,'TIM',10,'GERMANY'); INSERT INTO POSTINGS VALUES(6,'RYAN',11,'GERMANY'); INSERT INTO POSTINGS VALUES(7,'RYAN',10,'GERMANY'); INSERT INTO POSTINGS VALUES(8,'RYAN',10,'GERMANY'); INSERT INTO POSTINGS VALUES(9,'JACK',6,'CUBA'); INSERT INTO POSTINGS VALUES(10,'JACK',6,'HAITI');
Output:
Step 6: Display all the rows of the POSTINGS table.
Query:
SELECT * FROM POSTINGS;
Output:
Step 7: Find duplicates in 3(multiple) columns i.e. in OFFICER_NAME, TEAM_SIZE and POSTING_LOCATION in the table POSTINGS. To achieve the, we need to group the records by these three columns and display those which have the count greater than 1 i.e. have matching values. Use the keywords GROUP BY and COUNT.
Syntax:
SELECT COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3, COUNT(*) AS ALIAS FROM TABLE_NAME GROUP BY COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3 HAVING COUNT(*)>1;
Query:
SELECT OFFICER_NAME, TEAM_SIZE, POSTING_LOCATION, COUNT(*) AS QTY FROM POSTINGS GROUP BY OFFICER_NAME, TEAM_SIZE, POSTING_LOCATION HAVING COUNT(*)>1;
Output:
Step 8: Find duplicates in 2(multiple) columns i.e. in TEAM_SIZE and POSTING_LOCATION in the table POSTINGS.
Query:
SELECT TEAM_SIZE, POSTING_LOCATION, COUNT(*) AS QTY FROM POSTINGS GROUP BY TEAM_SIZE, POSTING_LOCATION HAVING COUNT(*)>1;
Output:
Step 9: Find duplicates in 2(multiple) columns i.e. in OFFICER_NAME and TEAM_SIZE in the table POSTINGS.
Query:
SELECT OFFICER_NAME, TEAM_SIZE, COUNT(*) AS QTY FROM POSTINGS GROUP BY OFFICER_NAME, TEAM_SIZE HAVING COUNT(*)>1;
Output:
Contact Us