Can Someone help me write a query to determine which team lost a match AND a separate query to count-CodePudding

I have tried the following code.

SELECT  (Team 1) OR ( Team 2) AS[Losser], Winner
FROM MatchesTbl
WHERE (WINNER NOT LIKE (Team 1))

I have attached an image of the table I am working with. I am working in Microsoft access. I am still very new to programming and I am trying to do this for a school project.

CodePudding user response：

Here is the original query -- as you an see I left some extra columns here (to make it clearer and you might want to use this for something else)?

SELECT Loser, COUNT(*)
FROM (
  SELECT  MatchID, [Match Date], Tournamer, [Team 1], [Team 2], Winner,
    CASE WHEN [Team 1] = Winner THEN [Team 2]
       WHEN [Team 2] = Winner THEN [Team 1]
       ELSE 'Unknown Loser'  
    END AS Loser
   FROM MatchesTbl 
) X
GROUP BY Loser

Here is the cleaned up answer:

SELECT Loser, COUNT(*)
FROM (
  SELECT 
    CASE WHEN [Team 1] = Winner THEN [Team 2]
       WHEN [Team 2] = Winner THEN [Team 1]
       ELSE 'Unknown Loser'  
    END AS Loser
   FROM MatchesTbl 
) X
GROUP BY Loser

also note, that the answer to your first question in the inner query and the answer to the second question is the outer query

CodePudding user response：

If you use MS Access you can use the IIF function to get the first result

SELECT T.Team1, T.Team2, T.Winner, IIf([Team1]=[Winner],[Team2],[Team1]) AS Loser
FROM tblMatches AS T;

Please note that I modified your orignal table name and field names a little bit.

In MS Access you can save that query and based on the saved query (I saved it as qryLoser) you can get your second result

SELECT qryLoser.Loser, Count(qryLoser.Loser) AS CountOfLoser
FROM qryLoser
GROUP BY qryLoser.Loser;