By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table world(continent varchar(30), population int, country varchar(30));
insert into world values
('Cont1',1500000000,'Country1-1')
,('Cont1',1500000000,'Country1-2')
,('Cont1', 10000,'Country1-3')
,('Cont2', 10000,'Country2-1')
,('Cont2', 100000,'Country2-2')
,('Cont2', 1000000,'Country2-3')
,('Cont2', 10000000,'Country2-4')
,('Cont3', 1000000,'Country3-1')
;
Records: 8 Duplicates: 0 Warnings: 0
select distinct continent
,(select count(*) from world w2 where w2.continent=w.continent and population>=10000000) cnt
from world w
| continent | cnt |
|---|---|
| Cont1 | 2 |
| Cont2 | 1 |
| Cont3 | 0 |