Access SQL subquery WHERE clause doesn’t filter results [closed]

I have a problem with a query in an Access database. The database would be for storing and managing basic statistics of a sportsleague. This is my query string:

SELECT Participants.Name AS Name
, Count(Games.Participants) AS NumberOfGames
, (

     SELECT Name
     , Count(Games.Winner)
     FROM Participants
     INNER JOIN Games
          ON Participants.ID = Games.Winner
     WHERE Participants.ID = Games.Winner

   ) AS Won
FROM Participants
INNER JOIN Games 
     ON Participants.ID = Games.Participants.Value
GROUP BY Participants.Name
ORDER BY Participants.Name;

For some reason, the subquery returns the number of all records, not just the ones where the ID of the winner matches with the ID of the participant, and I can’t figure out why. The content of the subquery works when it isn’t in a subquery. And when I explicitly state the ID of a participant in the WHERE clause of the subquery, it returns correct value for that ID. But I can’t make it work the way I need it.

Answer

I’m guessing that you’ll want to use conditional aggregation:

select 
    p.name as name, 
    count(*) as numberofgames, 
    sum(iif(g.winner = p.id,1,0)) as won
from 
    participants p inner join games g on p.id = g.participants
group by 
    p.name
order by 
    p.name

Here p & g are merely aliases to save typing out the table names repeatedly.

Advertisement

Answer