Use the value from SELECT in WHERE

Question

So, I choose the name and a number of sports that this name is associated with, and I need to choose them only when the number of sports achievements is more than 1.

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

If WHEREdeleted, the query works just fine and displays all the lines, some of which have only "1" as NrOfSports. When I add it to a sentence WHERE, I get an error because it is not recognized. This puzzles me, because if I used it in another column SELECT, it would work fine.

Is there any way to do this? It cannot be software dependent.

Answer 1

Group By Having:

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

, , where. , , Group By .

Answer 2

, WHERE . , , :

(1) WHERE NrOfSports > 1

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

(2) .

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

, , (!)

GROUP BY WHERE HAVING, name, 1 id.

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

Answer 3

Sgeddes answer is better than mine. But you can also make this request:

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