benjamin benjamin - 2 years ago 105
SQL Question

MySQL selection based on more than one count

how can i list specific items from my table based on more than one count?

the Table countrylanguage table is of the form

CountryCode, Language, IsOfficial, Percentage**
ABW Dutch T 5.3
AFG Balochi F 0.9
AFG Dari T 32.1

I have come out with this code so far but this does not give me the right output

select countrycode, Language, IsOfficial, count(*)



group by countrycode

and the sample output is like this:

sample output

Example: "Aimara'" is the official language of Bolivia and Peru', and it's also spoken in Chile but not as an official language. So the query should return the records:

Aimara Bolivia T
Aimara Chile F
Aimara Peru T

but for example 'Afrikaans' should not be returned, because it's spoken in two countries (Namibia and South Africa) but it's only official in one.

For each language that is official in more than one country, how can I list the name of each of the countries in which that language is spoken (indicating whether or not it is official in that country)?

Answer Source

You can use a subquery with having and in clause

select countrycode,  Language, IsOfficial
   where language in  
    (select  Language
    from countrylanguage
    where IsOfficial = 'T'
    having count(*)>1
    group Language ) 

order by language;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download