How to count repeat emails in a Mysql table?

Asked

Viewed 240 times

4

I have a table where some registrations were made using the same email. I would like to count these repeated emails, for this I am using this way:

SELECT COUNT(DISTINCT email) AS Contar FROM cadastro;

What I find strange, is the quantity returned and why I am unsure to use this query. I would like you to return:

There are 110 repeat emails

php mysql

  • And what was the return obtained?

  • So.. he’s returning me many registrations, the amount is: 6050 and the registration has 7543. I’m finding too much email repeated.

  • 1

    Yes, what you’re calculating are separate emails, because you used DISTINCT. The difference from this value to the total is repeated emails.

  • @Andersoncarloswoss this will only be true if an email does not repeat more than once.

  • @rLinhares If I have the records (a, a, a, b, c, c) the result with DISTINCT will be 3, because there are 3 different values, while the total is 7, indicating that there are 4 repeated records (3 a and 1 c)

  • @Andersoncarloswoss you’re right! I confused the balls but I already found myself =p Sorry (I even corrected my answer - I hope I have corrected right)

Show 1 more comment

2 answers

3

Create a sub-link to know which emails are repeating:

SELECT COUNT(qtd)
FROM (SELECT COUNT(*) AS qtd FROM cadastro GROUP BY email HAVING COUNT(*) > 1) as TB_TESTE

test running

  • Hello rLinhares. I tried your code but gave this error: Você tem um erro de sintaxe no seu SQL próximo a '*) FROM (SELECT COUNT(*) AS qtd FROM cadastro GROUP BY email HAVING COUNT(*) > 1' na linha 1

  • @Fox.11 corrected answer! besides the syntax error I realized I should use count instead of sum =p I will add one fiddle also.

1


If you just want to list the repeated ones use the HAVING clause:

SELECT email, COUNT(*) FROM cadastro 
GROUP BY email
HAVING (COUNT(*) > 1);

  • Hello anonimo, actually I do not need to list but bring the total.

  • But the above query provides for each repeated email the amount of repetitions of this existing email in the registration.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.