CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 3968

Count the number of occurrences of each email address

I have a mySQL workbench table called table_contacts, with the fields:

user_id and PrimaryEmail

I want to write a query that, for each row in the table will return:

User_id, PrimaryEmail and Number of occurrences of that email address in the table. So I want the following table to be returned:

enter image description here

I know I need to use a sub query. So far I have:

select user_id, PrimaryEmail, 
(select Count(PrimaryEmail) from table_contacts where PrimaryEmail = table_contacts.PrimaryEmail) 
from table_contacts

But this is returning the count of all email addresses in the table.

What am I doing wrong?

Upvotes: 0

Views: 3879

Answers (5)

Sim1
Sim1

Reputation: 532

try this:

select user_id, PrimaryEmail, Count(PrimaryEmail)
from table_contacts
group by PrimaryEmail

in SQL tryit editor a similar query would be

SELECT customerid,count(country),country FROM [Customers] group by country

but in this case you receive only the count of each email (one row for each email). Other (better) solutions have been proposed if you want to list all the rows with the couunt added.

Upvotes: 2

kubica
kubica

Reputation: 54

This should do the job:

select t1.user_id, t1.PrimaryEmail, count(*) 
from table_contacts t1 
     join table_contacts t2 on t1.PrimaryEmail = t2.PrimaryEmail 
group by t1.user_id, t1.PrimaryEmail 
order by t1.user_id;

Upvotes: 0

chamarais
chamarais

Reputation: 21

You need a group by, not a subquery

something like

select user_id, PrimaryEmail, Count(PrimaryEmail)
from table_contacts
group by PrimaryEmail

Upvotes: 2

djoosi
djoosi

Reputation: 236

The solution of Simone and Grażynka will group by address, so you will lose some row each time the email address is more than one time.

To display all row with a count of same email, you can do :

SELECT t1.user_id, t1.PrimaryEmail, (SELECT COUNT(*) FROM table_contacts t2 WHERE t2.PrimaryEmail = t1.PrimaryEmail) FROM table_contacts t1

Upvotes: 3

Grażynka
Grażynka

Reputation: 29

Try this one:

Select user_id, primaryemail, count(*)
 from table_contacts
group by user_id, primaryemail

Upvotes: 2

Related Questions