GROUP BY Subquery returns more than one row

I’m looking for a way to solve the following situation. I have a table that I need to return only one number for each “p.pays“, This query is supposed to list “nom from table Pays” where at least half of the “athlete” have are in the table “Resultat” but my subquery returns more than one line is there a way I can match “p.code” in both the query and the subquery so it only returns 1 line per “p.code“.

SELECT p.nom , count(*) FROM Athlete a
INNER JOIN Pays p ON a.pays = p.code
GROUP BY p.code HAVING count(*)/2 >= (SELECT count(*) FROM Athlete a
                    INNER JOIN Pays p ON a.pays = p.code
                    INNER JOIN Resultat r ON a.code = r.athlete
                    GROUP BY p.code);

Expected result, show Countries”Pays” where at least half of the athletes “Athlete” have won a medal (Athlete is in the Resultat table). :

  p.nom    | count(*)
|----------|--------|
|Albania   | 134    | <-- Total Number of athletes "Athlete" in the 
|Argentina | 203    |                    country "Pays".
| ...      | ...    |

Answer

You want to have two counts of athlethes in the country:

all athletes
the resultat athletes

Use a conditional count for this:

SELECT p.nom, count(*)
FROM pays p
INNER JOIN athlete a ON a.pays = p.code
GROUP BY p.code
HAVING COUNT(*) / 2 >= 
       COUNT(*) FILTER (WHERE a.code IN (SELECT athlete FROM resultat))
ORDER BY p.nom;

Advertisement

Answer