Forum

Forums
SQL
Interview Questions...
Write an SQL query ...
 
Share:
Notifications
Clear all

Write an SQL query to find duplicate records in a table and display the count of duplicates.

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:04 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

To find duplicate records, we use the GROUP BY clause along with HAVING to filter rows where the count of occurrences exceeds one.

SQL Statement:

sql
 
SELECT column_name, COUNT(*) AS occurrence_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

Explanation:

  1. SELECT Statement: Choose the column(s) you want to check for duplicates. Replace column_name with the actual column name (or a combination of columns) relevant to your table.
  2. GROUP BY Clause: This clause groups rows with the same value in the column_name.
  3. HAVING Clause: Filters the grouped results to show only those with a count greater than 1, indicating duplicates.
  4. COUNT Function: Returns the number of occurrences of each value in column_name.

Example: Assume you have a table named employee with the following columns:

  • employee_id
  • name
employee_id name
1 John
2 Alice
3 John
4 Bob
5 Alice

Query:

sql
 
SELECT name, COUNT(*) AS occurrence_count
FROM employee
GROUP BY name
HAVING COUNT(*) > 1;

Result:

name occurrence_count
John 2
Alice 2

Use Case: This query helps identify duplicate entries in tables, which is essential for data cleansing, auditing, or ensuring data integrity.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: