MySQL Comparison Query

Question

I need to compare all regions and show the better regions about the score. For example if I want to see Asia's Score Comparison; I will see only the better regions about the score. (better regions: North America, Europe)

select
    Destination_Region,
    count(1) as Score
from pro11
    where 
        Destination_Region is not null
    group by 
        Destination_Region 
    order by 
        Score DESC;

Query Result:

Europe             1069737
North America      218302
Asia               140452
Middle East        70899
Africa             33901
Oceania            28701
South America      24815
Caribbeans         16550

So if I want to see Africa's comparison results; I should see the Middle East, Asia, North America, Europe. I will make the query dynamic, you could pick a region and use it.

Thanks!

Answer 1

Check it using HAVING clause:

select
    Destination_Region,
    count(*) as Score
from pro11
    where 
        Destination_Region is not null
    group by 
        Destination_Region 
    having  Score>(SELECT COUNT(*) FROM pro11 WHERE Destination_Region = 'Africa' GROUP BY Destination_Region)
    order by Score DESC;

Sample result in SQL Fiddle.