Find Duplicate Records: GROUP BY with HAVING COUNT > 1

• GROUP BY duplicate_key – buckets rows by the column or set of columns that defines "sameness," such as PAN number or email.
• **COUNT(*)** – tallies how many rows fall into each bucket.
• **HAVING COUNT(*) > 1** – discards buckets with only one row, keeping only the values that repeat.
• Window function approach – COUNT(*) OVER (PARTITION BY key) annotates every row without collapsing, so you can retrieve the actual duplicate rows.
• UNIQUE constraint – the preventive fix that stops duplicates from being inserted in the first place.

What does it mean to find duplicate records?

Duplicate records are rows that share the same value in a column (or set of columns) where uniqueness is expected. At the National Bank of India, Priya discovered that some customers registered twice with the same PAN number because the sign-up flow lacked a unique constraint and two near-simultaneous registrations both succeeded.

-- Sample table with duplicates
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100),
    pan  VARCHAR(10),
    email VARCHAR(200)
);

INSERT INTO customers (name, pan, email) VALUES
('Rajesh Kumar', 'ABCDE1234F', 'rajesh@bank.com'),
('Rajesh K.',    'ABCDE1234F', 'rajesh.k@bank.com'),
('Priya Sharma', 'FGHIJ5678K', 'priya@bank.com'),
('Pankaj Gupta', 'LMNOP9012Q', 'pankaj@bank.com');
-- PAN 'ABCDE1234F' appears twice

How do you find which values are duplicated?

Group by the duplicate key, count each group, and keep groups with a count greater than one.

SELECT pan, COUNT(*) AS cnt
FROM   customers
GROUP BY pan
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

 pan         | cnt
-------------+-----
 ABCDE1234F  |   2

This tells you which PAN values are duplicated and how many times, but it returns one row per duplicated value, not the actual duplicate rows.

Why does the count condition go in HAVING, not WHERE?

SQL evaluates clauses in this order: FROM, WHERE, GROUP BY, aggregates, HAVING, SELECT, ORDER BY. WHERE runs before grouping, so the group count does not exist yet at that point. HAVING runs after grouping and aggregation, so it can see the count. Writing WHERE COUNT(*) > 1 is a syntax error.

-- ERROR: cannot use aggregate in WHERE
SELECT pan, COUNT(*) FROM customers
WHERE COUNT(*) > 1
GROUP BY pan;
-- ERROR: aggregate functions are not allowed in WHERE

-- CORRECT: use HAVING
SELECT pan, COUNT(*) FROM customers
GROUP BY pan
HAVING COUNT(*) > 1;

How do you retrieve the actual duplicate rows?

The group-count query collapses rows, so you lose the individual records. To get the full rows, use a window function that annotates each row without collapsing.

SELECT *
FROM (
    SELECT c.*,
           COUNT(*) OVER (PARTITION BY pan) AS pan_count
    FROM   customers c
) t
WHERE pan_count > 1
ORDER BY pan;

 id | name          | pan         | email              | pan_count
----+---------------+-------------+--------------------+----------
  1 | Rajesh Kumar  | ABCDE1234F  | rajesh@bank.com    |         2
  2 | Rajesh K.     | ABCDE1234F  | rajesh.k@bank.com  |         2

How do you handle case and whitespace variations?

Emails and names often have case or whitespace differences that should count as duplicates. Normalize the key inside the GROUP BY with LOWER and TRIM.

-- Without normalization: these look distinct
-- 'Rajesh@bank.com' vs 'rajesh@bank.com ' (trailing space)

-- CORRECT: normalize before grouping
SELECT LOWER(TRIM(email)) AS normalized_email,
       COUNT(*) AS cnt
FROM   customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

How do you remove duplicates, keeping one copy?

Use ROW_NUMBER() to assign a sequence within each duplicate group, ordered by id (keeping the earliest), and delete rows where the number exceeds one.

-- Step 1: identify which rows to keep (rn = 1) and which to delete (rn > 1)
SELECT id, pan,
       ROW_NUMBER() OVER (PARTITION BY pan ORDER BY id) AS rn
FROM   customers;

 id | pan         | rn
----+-------------+----
  1 | ABCDE1234F  |  1  ← keep
  2 | ABCDE1234F  |  2  ← delete
  3 | FGHIJ5678K  |  1  ← keep
  4 | LMNOP9012Q  |  1  ← keep

-- Step 2: delete duplicates
DELETE FROM customers
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY pan ORDER BY id) AS rn
        FROM   customers
    ) t
    WHERE rn > 1
);

What about NULL values in the duplicate key?

Two NULLs are not considered equal by standard SQL, so GROUP BY treats each NULL as its own group, and they will not be flagged as duplicates. If NULLs should count as duplicates, use COALESCE to replace them with a sentinel value.

-- NULLs are NOT grouped together by default
SELECT pan, COUNT(*) FROM customers
GROUP BY pan HAVING COUNT(*) > 1;
-- Two rows with pan = NULL are NOT flagged

-- Fix: replace NULL with a sentinel
SELECT COALESCE(pan, 'NO_PAN') AS pan_key, COUNT(*)
FROM   customers
GROUP BY COALESCE(pan, 'NO_PAN')
HAVING COUNT(*) > 1;

How do you prevent duplicates in the first place?

Finding duplicates after the fact is reactive. The durable fix is a UNIQUE constraint or unique index on the key so the database refuses to insert the second copy.

-- Add a unique constraint (after cleaning existing duplicates)
ALTER TABLE customers ADD CONSTRAINT uq_customers_pan UNIQUE (pan);

-- Or a unique index with normalization
CREATE UNIQUE INDEX idx_customers_email_unique
    ON customers (LOWER(TRIM(email)));

-- Now a duplicate insert fails
INSERT INTO customers (name, pan, email)
VALUES ('Duplicate', 'ABCDE1234F', 'dup@bank.com');
-- ERROR: duplicate key value violates unique constraint "uq_customers_pan"

What should a senior engineer know?

• For multi-column duplicates, group by all columns that define sameness: GROUP BY first_name, last_name, dob.
• In PostgreSQL, DISTINCT ON (pan) ... ORDER BY pan, id is an alternative way to keep one row per duplicate key.
• For very large tables, the group-count approach is faster than a self-join because it scans the table once.
• In production, always run the SELECT first to verify which rows will be deleted before executing the DELETE.
• Consider soft-deleting duplicates (marking a status column) rather than hard-deleting, so you can audit what was removed.

What are common interview follow-up questions?

• How do you find duplicates across two columns? (GROUP BY col1, col2 HAVING COUNT(*) > 1.)
• How do you count how many duplicates exist in total? (Subtract the count of distinct keys from the total row count.)
• What is the difference between HAVING COUNT(*) > 1 and HAVING COUNT(column) > 1? (COUNT(*) counts all rows; COUNT(column) counts non-NULL values only.)