CODEWITHSUNDEEP
sqlite
user2520451
user2520451

Reputation: 63

Find the number of duplicates in a row using sqlite

I have a database table which has entries with 8 numbers associated with each of the entry name . So i need to write a Query to get the total number of contact names which has duplicate numbers associated with each of the contact name. I have a sqlite table as shown below schematics

Name | Num0 | Num1 | Num2 | Num3 | Num4 | Num5 | Num6 | Num7

Assume that i have 6000 entries and there are 100 entries which has duplicate numbers which can fall in any of the columns starting from TelNum0 to TelNum8.

For example: As shown below i have a Database which has 5 entries total having 5 names like:

Name       Num0    Num1    Num2   Num3   Num4  Num5   Num6  Num7  
1)John     1234    1234    1234 

2)Hebbar   234   

3)Jim      9876    9876    9876

4)Kim      111     111     111

5)Kate     666

Now when i run a query, i should get the number of results as 3 since John, Jim and Kim had duplicate entries associated with them .

Thanks a lot in advance

Upvotes: 1

Views: 97

Answers (1)

CL.
CL.

Reputation: 180020

First, properly normalize the data:

SELECT Name, Num0 AS Num FROM MyTable WHERE Num0 IS NOT NULL
UNION ALL
SELECT Name, Num1        FROM MyTable WHERE Num1 IS NOT NULL
UNION ALL
SELECT Name, Num2        FROM MyTable WHERE Num2 IS NOT NULL
UNION ALL
SELECT Name, Num3        FROM MyTable WHERE Num3 IS NOT NULL
UNION ALL
SELECT Name, Num4        FROM MyTable WHERE Num4 IS NOT NULL
UNION ALL
SELECT Name, Num5        FROM MyTable WHERE Num5 IS NOT NULL
UNION ALL
SELECT Name, Num6        FROM MyTable WHERE Num6 IS NOT NULL
UNION ALL
SELECT Name, Num7        FROM MyTable WHERE Num7 IS NOT NULL;

Name   Num
------ ----
John   1234
John   1234
John   1234
Hebbar 234
Jim    9876
Jim    9876
Jim    9876
Kim    111
Kim    111
Kim    111
Kate   666

Then just count the duplicates:

SELECT Name, Num, COUNT(*)
FROM (SELECT Name, Num0 AS Num FROM MyTable WHERE Num0 IS NOT NULL
      UNION ALL
      SELECT Name, Num1        FROM MyTable WHERE Num1 IS NOT NULL
      UNION ALL
      SELECT Name, Num2        FROM MyTable WHERE Num2 IS NOT NULL
      UNION ALL
      SELECT Name, Num3        FROM MyTable WHERE Num3 IS NOT NULL
      UNION ALL
      SELECT Name, Num4        FROM MyTable WHERE Num4 IS NOT NULL
      UNION ALL
      SELECT Name, Num5        FROM MyTable WHERE Num5 IS NOT NULL
      UNION ALL
      SELECT Name, Num6        FROM MyTable WHERE Num6 IS NOT NULL
      UNION ALL
      SELECT Name, Num7        FROM MyTable WHERE Num7 IS NOT NULL)
GROUP BY Name, Num
HAVING COUNT(*) > 1;

Upvotes: 2

Related Questions