Find the Duplicate values and Number of occurrence of them in a column of a Table using SQL line query

Here we will discuss how to find the duplicity in a column of a Table in SQL.

Step 1: Create a table named ‘Tbl’ with a column named ‘ID’ of int type.

create table Tbl
(
ID int
)

Step 2: Insert some rows of ‘0’ ,‘1’and ‘02’ values.

insert into Tbl values (0)
insert into Tbl values (1)
insert into Tbl values (0)
insert into Tbl values (2)
insert into Tbl values (0)
insert into Tbl values (1)

Step 3: Select the Table to see the table.

select * from Tbl

Question: Now I want to find the Duplicate values in the ‘ID’ column and number of occurrence of them, for example the ’0’ value shall exist in 3 rows and ‘1’ value shall exist in 2 rows.

Note:-‘2’ value will not be displayed because it exists in a 1 row.

Answer: The easiest way to resolve this task is mentioned below.

select ID,COUNT(ID) as NumberOfOccurance from Tbl group by ID having (COUNT(ID)>1)

Explanation

1. COUNT(ID) as NumberOfOccurance:- Counting the number of occurrence of a ID.
2. group by – It is necessary in aggregate function like ‘count()’ otherwise it will give error.
3. having (COUNT(ID)>1) -Select those rows which count of ID value is greater than 1, that’s why it shows ‘0’ and ‘1’.
   
   

pasting