How to solve errors in MySql subquery-CodePudding

I'm learning Mysql, a newbie. First I wrote:

Select name, count(*)
From Emp
Group By name;

and the code was successful. After that, I added to the code it became:

Select *
From Emp
Where (Select name, count(*)
From Emp
Group By name) > 1;

and I get an error message too many values. How to fix that?

CodePudding user response：

To get the name which have the count >1 use having count clause.

Try:

Select name, 
       count(*)
From Emp
Group By name
having count(name) > 1;

Learn more on: https://www.mysqltutorial.org/mysql-count/

select e.*
from Emp e 
INNER JOIN (Select name, 
                   count(*)
            From Emp
            Group By name
            having count(name) > 1
           ) as e2 on e2.name=e.name

CodePudding user response：

To get all the information of the employees that have the same name, you can do folowing

Select e1.*
From Emp  e1 INNER JOIN (Select name
From Emp
Group By name
having count(name) > 1) e2 ON e2.name = e1,name