Search code examples
sqloracle-sqldeveloper

To display manager details if more than one manager is from same city

I have a table manager(managerid, name, address, city, phone). I have got to display the city, name and phone details if more than one manager is from the same city. My code is:

  select m.city, m.name, m.phone
    from manager m
group by m.name
  having count(m.city) > 1;

But this shows me an error in the first line saying "not a group by expression".
Please help!

Solution

  • A simple method uses exists:

    select m.*
from manager m 
where exists (select 1
              from manager m2
              where m2.city = m.city and m2.managerid <> m.managerid
             );

    You can also use count() as a window function:

    select m.*
from (select m.*, count(*) over (partition by city) as cnt_city
      from manager m
     ) m
where cnt_city > 1;