CODEWITHSUNDEEP
mysqlcountwhere-clause
stytown
stytown

Reputation: 1724

Using COUNT with WHERE in MySQL

Having a bit of trouble. I need to find all of the countries that have a common language. I only need to list the countries in which the language happens more than once. I'm having a bit of trouble with this code.

-- group countries by a common language
SELECT country, language
FROM countryinfo
HAVING COUNT(language) > 1
GROUP BY language

Upvotes: 1

Views: 2650

Answers (3)

ruakh
ruakh

Reputation: 183270

If you want what I think you want, I think the clearest approach is to use a subquery; either this:

SELECT country, language
  FROM countryinfo
 WHERE language IN
        ( SELECT language
            FROM countryinfo
           GROUP BY language
          HAVING COUNT(1) > 1
        )
;

or this:

SELECT country, language
  FROM countryinfo ci1
 WHERE EXISTS
        ( SELECT 1
            FROM countryinfo ci2
           WHERE ci2.language = ci1.language
             AND ci2.country <> ci1.country
        )
;

Note that this gives a different result from other answers, because what I think you want is different from what other answerers think you want. You'll have to try these queries out and figure out which is what you meant.

Edited to add: here's an alternative theory about what you want:

SELECT GROUP_CONCAT(country),
       language
  FROM countryinfo
 GROUP BY language
HAVING COUNT(1) > 1
;

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230306

If you group by language, then you need to count countries.

SELECT country, language
FROM countryinfo
GROUP BY language
HAVING COUNT(country) > 1

Upvotes: 1

cmc
cmc

Reputation: 4413

It's in the positioning. This should work.

-- group countries by a common language
SELECT country, language
FROM countryinfo
GROUP BY language
HAVING COUNT(language) > 1

Upvotes: 1

Related Questions