CODEWITHSUNDEEP
mysqlsql
Shiva Krishna
Shiva Krishna

Reputation: 13

Select rows which are having same values one or more

enter image description here

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

enter image description here

Thanks for your help! Please see the attached images, I ran the query as you suggested but still, I got the same output (highlighted in red in excel). I want to exclude those emailIds which has only one phone number and include all emailIds which have more than one phone number. [![enter image description here][3]][3] output [![enter image description here][4]][4]

Upvotes: 0

Views: 302

Answers (1)

Mihe
Mihe

Reputation: 2318

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

profile phone country
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

The query above results in

profile phone country
a 1 AZ
b 1 AZ
c 2 AU
d 2 AU
g 4 CA
h 4 CA

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;

Upvotes: 1

Related Questions