I have two tables: Score table and Teams table. They both contain some foreign keys from other tables, but in my case I don't need columns from other tables.

I want to get the team id and points in a SELECT query. The points are made from a COUNT of MAX score of a team per round.

Score table:

Teams table:

As you can see a team have a tournament_id foreign key from a Tournaments table and Score table have a round_id foreign key from a Rounds table but I don't need these details in this step.

As you can see in Score table, for a round there are two records, one for each team that plays in that round and I want to know for any team how many times did it won a round (that's why I'm guessing I need a COUNT of MAX).

My SELECT query that I tried:

SELECT team_id, count(max(score)) as points FROM Score
WHERE team_id IN (SELECT DISTINCT t.id 
    FROM Teams t
    WHERE t.tournament_id = '9aaa6f86-5555-49b8-99a6-900a90dc3c7c')
GROUP BY round_id

But this query it throw me the Error Code: 1111. Invalid use of group function.

Expected result :

How am I supposed to get to the desired result?

Thank you for your time! If something is unclear or there should be more details, let me know!

CodePudding user response：

If I understand correctly you can try OUTER JOIN with subquery which get max score by each round_id

SELECT s.team_id,
       COUNT(m_Score)
FROM Score s 
LEFT JOIN 
(
    SELECT round_id,MAX(score) m_Score
    FROM Score 
    GROUP BY round_id
) t1 ON t1.m_Score = s.Score and t1.round_id = s.round_id
WHERE team_id IN (
    SELECT t.id 
    FROM Teams t
    WHERE t.tournament_id = '9aaa6f86-5555-49b8-99a6-900a90dc3c7c'
)
GROUP BY s.team_id

sqlfiddle