Use a value from SELECT in WHERE

Question

So I'm selecting a name and a number of sports that name is related to, and I need to only select them when that number of sports is greater than 1.

SELECT DISTINCT name AS FullName,  
      (SELECT COUNT(id) FROM coaches WHERE coaches.name=FullName) AS NrOfSports  
FROM coaches WHERE NrOfSports>1

If WHERE is removed the query works just fine and displays all rows of which some have only "1" as NrOfSports. When I add it to the WHERE clause I get an error because it's not recognized. This baffles me since if I were to use it in another SELECT column it would work fine.

Is there a way to do this? It can't be software dependant.

Answer 1

Use Group By and Having instead:

SELECT name AS FullName, 
   COUNT(id) AS NrOfSports 
FROM coaches 
GROUP BY name
HAVING COUNT(id) > 1

Your correlated query can work, you just need to move it to a subquery and then you can add the where criteria. However, I believe the group by would be faster.

Answer 2

The answer of sgeddes is better than mine. But you could do also do this query :

    SELECT * 
    FROM (
       SELECT DISTINCT name AS FullName,  
             (SELECT COUNT(id) FROM coaches WHERE coaches.name=FullName) AS NrOfSports  
       FROM coaches  
   ) tmp  
     WHERE NrOfSports>1