CODEWITHSUNDEEP
sqlwhere-clause
rich2165990
rich2165990

Reputation: 111

How do I retrieve the count of each set of records that matches a certain WHERE criteria?

To give you an idea of what I'm trying to do. The simplified table structure would be: 

homeTeamName | awayTeamName | homeTeamGoals | awayTeamGoals
-----------------------------------------------------------
Team1        | Team2        |      4        |       3
Team1        | Team3        |      0        |       1
Team3        | Team2        |      0        |       0

The data is from a soccer game. What I'm trying to do is retrieve the 'form' team, i.e. the team that has won the most games (assume the above table is filled with a lot more data). The query, if I were looking to retrieve the 'win count' for a specific team is fairly easy, as seen below.

SELECT COUNT(*) as winCount FROM matches_new 
  WHERE (
      homeTeamName = 'Team1' AND 
      homeTeamGoals > awayTeamGoals
    ) OR (
      awayTeamName = 'Team1' AND 
      awayTeamGoals > homeTeamGoals
    )
  ORDER BY winCount DESC LIMIT 1

That query would return the amount of 'wins' a specific team would have. Now what I would like to do is for my query to check ALL teams and then return the name of the team who has the most 'wins' and also the amount of 'wins' (win count).

I genuinely don't have a clue how to do this! I've tried a lot of searching but it's difficult to even think how one would describe this type of task, or think of anything that's similar. My trouble seems to stem from the fact I don't know how you would do a WHERE clause without being specific about what a value should equal.

Thanks.

Upvotes: 1

Views: 105

Answers (2)

user1919238
user1919238

Reputation:

You could do it with a case statement:

select winningTeam, count(*) as winCount from (
    select case 
             when homeTeamGoals > awayTeamGoals then homeTeamName
             when awayTeamGoals > homeTeamGoals then awayTeamName
             else null
           end as winningTeam
        from matches_new
  ) winners
  where winningTeam is not null
  group by winningTeam
  order by winCount desc limit 1

Or you could solve it with a union:

SELECT teamName, COUNT(*) as winCount from (
     select homeTeamName as teamName from  matches_new 
        WHERE homeTeamGoals > awayTeamGoals
     union all
     select awayTeamName as teamName from  matches_new 
        WHERE awayTeamGoals > homeTeamGoals
  ) as allWinners
  GROUP BY teamName
  ORDER BY winCount DESC LIMIT 1

Upvotes: 2

rs.
rs.

Reputation: 27467

Try this

select teamname, count(*) winCount from 
(
select case when homeTeamGoals > awayTeamGoals then hometeamname
            when wayTeamGoals > homeTeamGoals then awayTeamName end Teamname
from matches_new
where (homeTeamGoals > awayTeamGoals or wayTeamGoals > homeTeamGoals)
)
group by teamname
ORDER BY winCount DESC LIMIT 1

Upvotes: 0

Related Questions