CODEWITHSUNDEEP
mysql
blueGOLD
blueGOLD

Reputation: 105

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(*)

from 

 countrylanguage

 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)?

Upvotes: 0

Views: 58

Answers (2)

gview
gview

Reputation: 15371

Using a correlated subquery will allow you to filter the languages to a list of only those that fit your criteria of being the primary language for 2 or more countries.

SELECT Language, CountryCode, isOfficial
FROM countrylanguage
WHERE Language IN (
    SELECT Language
    FROM countrylanguage
    WHERE isOfficial = "T"
    GROUP BY Language
    HAVING COUNT(*) > 1)
ORDER BY Language

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

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;

Upvotes: 1

Related Questions