Query to find duplicates between name and number in table

Question

SELECT count(*), lower(name), number FROM tbl GROUP BY lower(name), number HAVING count(*) > 1; 

input tb1

 slno name number 1 aaa 111 2 Aaa 111 3 abb 221 4 Abb 121 5 cca 131 6 cca 141 7 abc 222 8 cse 222 

This query can simply find duplicates in the number and names that are the same, but it cannot find duplicates in the 3rd and 4th row !!!

  SELECT count(*), lower(name) FROM tbl GROUP BY lower(name) HAVING count(lower(name)) > 1 

this query can find all duplicates by name !!! it works great

  SELECT count(*), number FROM tbl GROUP BY number HAVING count(number) > 1 

This query can find all duplicates in the room !!! it works great

I need a query that can find all duplicates both by name and by number, whether the name consists of lower case and upper case

 output name number count 2 111 aaa 2 --- abb 2 --- cca 2 222 --- 

Answer 1

Updated Question

"Get a duplicate both by number and by name" ... "name and number as another column"
Lines can be counted here twice!

 SELECT lower(name), NULL AS number, count(*) AS ct FROM tbl GROUP BY lower(name) HAVING count(*) > 1 UNION ALL SELECT NULL, number, count(*) AS ct FROM tbl GROUP BY number HAVING count(*) > 1; 

→ sqlfiddle

Original question

The problem is that query groups

 GROUP BY lower(name), number 

Since lines 3 and 4 have different number , they are not the same for this query.

If you want to ignore different numbers for this query, try something like:

 SELECT lower(name) , count(*) AS ct FROM tbl GROUP BY lower(name) HAVING count(*) > 1; 

Answer 2

With a bit of work, we can display the numbers for name and number in one column:

 select NameOrNumber, count(*) as Count from ( select name as NameOrNumber from tb1 union all select number from tb1 ) a group by NameOrNumber having count(NameOrNumber) > 1 

SQL Script Example # 1

Output number 1:

 | NAMEORNUMBER | COUNT | ------------------------ | 111 | 2 | | aaa | 2 | | abb | 2 | | cca | 2 | 

If you want output in separate columns, you can do something like this:

 select distinct if(t1.name = t2.name, t1.name, null) as DUPLICATE_Name, if(t1.number = t2.number, t1.number, null) as DUPLICATE_Number from tb1 t1 inner join tb1 t2 on (t1.name = t2.name or t1.number = t2.number) and t1.slno <> t2.slno 

SQL Script Example # 2

Output number 2:

 | DUPLICATE_NAME | DUPLICATE_NUMBER | ------------------------------------- | Aaa | 111 | | Abb | (null) | | cca | (null) |