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