Select all rows having duplicate phone numbers with different zone codes?

Question

I have a table called contacts with following fields:

+----+-------+--------------+
| id | name  | phone_no     |
+----+-------+--------------+

Suppose, I have have following values in this table :

+----+-------+--------------+
| id | name  | phone_no     |
+----+-------+--------------+
| 1  | Alex  | 9907661234   |--1, 2 are 
| 2  | Alex  | 09907661234  |--Same contacts but preceding with '0'
| 3  | John  | 9879612363   |--Same contacts but preceding with '91'
| 4  | John  | 919879612363 |-- 91 is (country code)
| 5  | Shawn | 9979867123   |
+----+-------+--------------+

I want to find number of duplicate contacts with duplicate numbers (here numbers preceded) with 0 and 91 are duplicates.

I want following output :

+------------+-------------+
| phone_no   |     cn      |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
+------------+-------------+

Answer 1

Assuming you have phone numbers are 10 chars (as you shown in your question) and optionally prefixed by some codes. Then you can use RIGHT(str,len) function in MySQL that return the specified rightmost number of characters.

The query will be as follows(read comments):

SELECT  RIGHT(`phone_no`, 10) as `mobile`,  -- selecting last 10 digits
        count(*) as `tatal_count`
FROM `table_name`
GROUP BY `mobile`  -- group by last ten digits
HAVING count(`mobile`) > 1;  -- if you want to select on duplicates

Working example:

Create table:

CREATE TABLE IF NOT EXISTS `details` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `phone` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`id`)
)

Insert Query:

INSERT INTO `details` VALUES 
("1", "Alex", "9907661234"),
("2", "Alex", "09907661234"),
("3", "John", "9879612363"),
("4", "John", "919879612363"),
("5", "Shawn", "9979867123");

[ANSWER]

mysql> SELECT  RIGHT(`phone`, 10) as `mobile`, 
    ->         count(*) as `tatal_count`
    -> FROM `details`
    -> GROUP BY `mobile`
    -> ;
+------------+-------------+
| mobile     | tatal_count |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
| 9979867123 |           1 |
+------------+-------------+
3 rows in set (0.04 sec)

Suppose if you wants only numbers those duplicates (more than one) then youcan use HAVING clause in MySQL:

mysql> SELECT  RIGHT(`phone`, 10) as `mobile`, 
    ->         count(*) as `tatal_count`
    -> FROM `details`
    -> GROUP BY `mobile`
    -> HAVING count(`mobile`) > 1;
+------------+-------------+
| mobile     | tatal_count |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
+------------+-------------+
2 rows in set (0.00 sec)

^{I am not check that codes are, and assuming you have valid mobile numbers in DB}