SQL Test Question need help using subselect

The question is : "which are the countries with a head of state which is the head of state of at least 2 countries"

and this is how the table looks

This is what I’ve tried so far but it only gives back countries with a headofstate count of 1

Select name, count(headofstate)
from country
where exists(select headofstate, count(name) from country group by headofstate having count(name) >= 2)
group by name

The expected result should be countries with a head of state which is the head of state of at least 2 countries but what I’m getting is countries with a headofstate count of 1

>Solution :

The subquery needs to be correlated with the main query, by matching the headofstate columns.

Select name
from country AS c1
where exists(
    select *
    from country AS c2
    WHERE c1.headofstate = c2.headofstate
    group by headofstate 
    having count(name) >= 2
)

There’s no need for grouping in the main query. Each country only appears once in the country table.

When you use a query in EXISTS() you don’t have to specify any columns to select, since it just tests whether the row exists, not the values.