From this table, i would like to select profiles which share the same phone number one or more times. Output should be:

CodePudding user response：

Probably you're looking for GROUP BY and HAVING:

SELECT * FROM profiles WHERE Phone IN (
    SELECT Phone
      FROM profiles
     GROUP BY Phone
    HAVING count(*) > 1);

Update

To make things more clear, I'll give an example:

CREATE TABLE profiles(
    profile varchar(10), 
    phone varchar(10), 
    country varchar(3));

INSERT INTO profiles VALUES
    ('a','1','AZ'), ('b', '1', 'AZ'), 
    ('c', '2', 'AU'), ('d', '2', 'AU'),
    ('f', '3', 'NZ'), ('g','4','CA'), 
    ('h', '4', 'CA'), ('i', '5', 'UK'),
    ('j', '6', 'US');

SELECT * FROM profiles;

will result in

The query above results in

Of course, one can also add the count:

SELECT profiles.*, number_of_phones 
  FROM profiles JOIN (
     SELECT Phone, count(*) number_of_phones
       FROM profiles
      GROUP BY Phone
     HAVING count(*) > 1) counts 
    ON profiles.phone=counts.phone;