CODEWITHSUNDEEP
mysqlcountduplicates
Gowtham
Gowtham

Reputation: 33

How to index rows with duplicate values in a column?

How can I count the duplicates in Phone?

For eg:

State   Zip Areacode    Phone
A       123 1           989
b       234 2           989
c       345 3           989
d       456 4           123
e       567 5           123
f       678 6           234
g       678 7           234

Expected result:

State   Zip Areacode    Phone   row_count
A       123 1           989     1
b       234 2           989     2
c       345 3           989     3
d       456 4           123     1
e       567 5           123     2
f       678 6           234     1
g       678 7           234     2

Upvotes: 2

Views: 1140

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28874

This is a Window function problem. For older MySQL versions (< 8.0), we can emulate it using Session variables. Try the following:

SELECT t1.State, 
       t1.Zip, 
       t1.Areacode, 
       @row_count := CASE 
                       WHEN @ph = t1.Phone Then @row_count + 1
                       ELSE 1 
                     END AS row_count, 
       @ph := t1.Phone AS Phone 
FROM 
  (SELECT State, 
          Zip, 
          Areacode, 
          Phone 
   FROM your_table 
   ORDER BY Phone) AS t1  
CROSS JOIN (SELECT @row_count := 1) AS init1 
CROSS JOIN (SELECT @ph := '') AS init2

PS: OP has confirmed that numbering within a partition of Phone can be anything.

Upvotes: 3

apokryfos
apokryfos

Reputation: 40730

A simple solution if you are ordering by phone number is:

SELECT State, Zip, Areacode, Phone, `Index` FROM (
    SELECT State, 
           Zip, 
           Areacode, 
           Phone, 
           @Idx := IF(@previous_phone=Phone, IFNULL(@Idx,0)+1,1) as Index,
           @previous_phone := Phone
    FROM table 
    ORDER BY Phone
) t;

This will just keep the track of the previous phone number and reset the index if it changes.

If you want to count duplicates for the combination Zip Area Phone then you can compare based on CONCAT(Zip,Area,Phone) e.g. 

SELECT State, Zip, Areacode, Phone, `Index` FROM (
    SELECT State, 
           Zip, 
           Areacode, 
           Phone, 
           @Idx := IF(@previous_phone=CONCAT(Zip, Area, Phone), IFNULL(@Idx,0)+1,1) as Index, 
           @previous_phone := CONCAT(Zip, Area, Phone)
    FROM table 
    ORDER BY Zip, Area, Phone
) t;

Upvotes: 0

Avdhesh Solanki
Avdhesh Solanki

Reputation: 144

Below is the MySQL query to find duplicate records by comparing multiple columns

SELECT 
    Zip, COUNT(Zip),
    Areacode,  COUNT(Areacode),
    Phone,      COUNT(Phone)
FROM
    TableName
GROUP BY 
    Zip , 
    Areacode , 
    Phone
HAVING  COUNT(Zip) > 1
    AND COUNT(Areacode) > 1
    AND COUNT(Phone) > 1;

Replace TableName form your Mysql table name.

Reference

Upvotes: 0

Related Questions