How to findi duplicate entries in MySQL

Question

I am trying this code:

SELECT Email FROM 
(SELECT Email, COUNT(Email) AS cnt
FROM Person
GROUP BY Email
HAVING cnt(*) >1 )

for this question: Not sure what I am getting wrong?

Here's the error I receive:

 Runtime Error Message: Line 6: SyntaxError: near '*) >1 )'
Last executed input: {"headers": {"Person": ["Id", "Email"]}, "rows": {"Person": []}}

As far as I can recall, 'cnt' isn't a valid function. Try 'count'. — MrVimes, Aug 09 '15 at 23:53

score 3 · Accepted Answer · answered Aug 09 '15 at 23:55

3

select email
from person
group by email
having count(*)>1

You don't need the nested queries for what you want.

answered Aug 09 '15 at 23:55

Blindy

65,249
10
91
131

score 1 · Answer 2 · answered Aug 09 '15 at 23:52

1

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(*) >1

`SQL FIDDLE`

answered Aug 09 '15 at 23:52

M.Ali

67,945
13
101
127

Thanks for introducing SQL Fiddle. But when I try your code it fails the tests `SELECT Email, COUNT(Email) AS cnt FROM Person GROUP BY Email HAVING COUNT(*) >1` – Mona Jalal Aug 09 '15 at 23:58
update your question with the query you have written, I cant see anything wrong with this query. – M.Ali Aug 10 '15 at 00:02

Vamsi Prabhala · Answer 3 · 2015-08-10T00:03:32.783

0

 select p.email from person p
 join (
 SELECT Email, COUNT(Email) AS cnt
 FROM Person
 GROUP BY Email
 HAVING count(email) > 1)  s
 on s.email = p.email

You can't use the alias of a column in the having or where clause.

edited Aug 10 '15 at 00:03

answered Aug 09 '15 at 23:55

Vamsi Prabhala

48,685
4
36
58

how the exists operator will evaluate to true for each row returned by the outer query :) , even if you do make a correlated query by adding a where clause in exists operator query, it will be an overkill of a fairly simple problem. – M.Ali Aug 10 '15 at 00:00
Thanks for the note. I ran your code here but doesn't give me the answer I am looking for; http://sqlfiddle.com/#!9/56c6a/1 – Mona Jalal Aug 10 '15 at 00:01
@vkp: here's the error I get from running your code: `{"headers": {"Person": ["Id", "Email"]}, "rows": {"Person": []}}` – Mona Jalal Aug 10 '15 at 00:02
got it @M.Ali..i modified it as the OP only needs the email column. – Vamsi Prabhala Aug 10 '15 at 00:04
modified the query @MonaJalal – Vamsi Prabhala Aug 10 '15 at 00:04
this is making a simple query look complex though. – Vamsi Prabhala Aug 10 '15 at 00:05

How to findi duplicate entries in MySQL

3 Answers3

SQL FIDDLE

`SQL FIDDLE`