CODEWITHSUNDEEP
mysqlsqlwhere-clause
ric
ric

Reputation: 101

How can I use AS and the alias in WHERE?

HI i want to check if NumOfSpeakLanguages has more than 10 languages, how can I do: here the code:

SELECT country.Name,country.Capital,COUNT(DISTINCT countrylanguage.Language) AS NumOfSpeakLanguages
FROM country,countrylanguage
WHERE country.Code=countrylanguage.CountryCode AND NumOfSpeakLanguages >10
GROUP BY country.Name

here the error code:

SELECT country.Name,country.Capital,COUNT(DISTINCT countrylanguage.Language) AS NumOfSpeakLanguages
FROM country,countrylanguage
WHERE country.Code=countrylanguage.CountryCode AND NumOfSpeakLanguages >10
GROUP BY country.Name LIMIT 0, 25

#1054 - Unknown column'NumOfSpeakLanguages' in 'where clause'

Thank you!

Upvotes: 1

Views: 54

Answers (1)

jarlh
jarlh

Reputation: 44696

Use HAVING clause for aggregate function conditions:

SELECT country.Name,country.Capital,COUNT(DISTINCT countrylanguage.Language) AS NumOfSpeakLanguages
FROM country,
JOIN countrylanguage
  ON country.Code = countrylanguage.CountryCode
GROUP BY country.Name
HAVING COUNT(DISTINCT countrylanguage.Language) > 10
LIMIT 0, 25

@Andrew Brēza commented: You could also say HAVING NumOfSpeakLanguages >10. (Which is a MySQL special.)

Also doing explicit JOIN!

Alternatively, do the aggregation in a a derived table:

select * from
(
    SELECT country.Name,country.Capital,COUNT(DISTINCT countrylanguage.Language) AS NumOfSpeakLanguages
    FROM country,
    JOIN countrylanguage
      ON country.Code = countrylanguage.CountryCode
    GROUP BY country.Name
) dt
where NumOfSpeakLanguages > 10
LIMIT 0, 25

Upvotes: 2

Related Questions