mysqlsqlcountsubqueryclause

How to solve errors in MySql subquery


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?


Solution

  • 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