Select COUNT of MAX in MySQL

Question

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:

id	round_id	team_id	score
1085e36b-7621-4634-93e2-c5404108ed23	424d4186-4432-4b8d-ab9d-00ee71945cf6	752f9ebc-7016-42f2-be90-768711fc3d46	18
47ff3765-6357-47bc-abb8-6b5adf436546	424d4186-4432-4b8d-ab9d-00ee71945cf6	90dd8de4-8ddc-46cd-8b67-d93edbede174	7
b26b1a5b-8606-4c08-b838-30fdbcf98697	3fbb49a0-7a8a-4db5-b854-84a9ce7ac2b3	90dd8de4-8ddc-46cd-8b67-d93edbede174	20
c1add78a-94a3-48cf-b89d-4e4500ab738d	3fbb49a0-7a8a-4db5-b854-84a9ce7ac2b3	752f9ebc-7016-42f2-be90-768711fc3d46	21

Teams table:

id	name	tournament_id
2b56a499-9fc4-40c2-a7c4-43762c2b27e7	2@1.com & 4@1.com	9aaa6f86-5555-49b8-99a6-900a90dc3c7c
752f9ebc-7016-42f2-be90-768711fc3d46	5@1.com & 3@1.com	9aaa6f86-5555-49b8-99a6-900a90dc3c7c
7c9aa32a-ff99-4169-9512-27bc0aac0093	7@1.com & 6@1.com	9aaa6f86-5555-49b8-99a6-900a90dc3c7c
90dd8de4-8ddc-46cd-8b67-d93edbede174	8@1.com & 1@1.com	9aaa6f86-5555-49b8-99a6-900a90dc3c7c

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 :

team_id	points
752f9ebc-7016-42f2-be90-768711fc3d46	2
90dd8de4-8ddc-46cd-8b67-d93edbede174	0

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!

Could you provide text sample data instead of image and what's your expect result from that? thanks — D-Shih, Apr 27 '22 at 07:06
A round means there is 2 rows in scores table per round_id? And I'm guessing these are uuids.? — P.Salmon, Apr 27 '22 at 07:14
@P.Salmon yes because on a round there are two teams that play (a team vs another team) so for any team I need the score — poPaTheGuru, Apr 27 '22 at 07:15
Are you not mistakenly using count(max(score)) instead of sum(max(score)) — Max Strandberg, Apr 27 '22 at 07:16
@MaxStrandberg Nope, because I do not care about the points that a team have, but I care about how many times did it won — poPaTheGuru, Apr 27 '22 at 07:22
Duplicate of [Error Code 1111. Invalid use of group function](https://stackoverflow.com/questions/22141968/error-code-1111-invalid-use-of-group-function). — user207421, Apr 27 '22 at 07:22
@user207421 not really because I do not have to check the score (it it's greater than a value or something else). Also, I don't really know if the problem is from the `GROUP_BY` or because of that `count(max(score))` — poPaTheGuru, Apr 27 '22 at 07:23
I suggest first try and tackle the error you are getting, as I can see the error states that you are using GROUP BY wrong, and that is true, group by is used when you are using an aggregate function. and when that is the case you need to group all the columns you selected except the aggregated ones. so in your case GROUP BY round_id should be changed to GROUP BY team_id. this should remove your error. but I don't believe that you will get the correct results. — Demeteor, Apr 27 '22 at 07:30
Apparently, single team (*752f9ebc...*) has won both rounds by 18-7 & 21-20 points. So your expected output should be 2 for this team & 0 for the other one. Am I right? — TalESid, Apr 27 '22 at 07:31
@Demeteor, indeed it doesn't matter if i `GROUP BY` team_id / round_id or both, but the error is thrown from using nested aggregate functions. Tho, I can't succed in creating a subquery with that — poPaTheGuru, Apr 27 '22 at 07:32
@TalESid, yes you are right (there was a mistake in the table, i have put 1 point for each team, but i have edited right now) — poPaTheGuru, Apr 27 '22 at 07:33
Also I can't see the use of Teams table here at all... Bcz in your expected output, there's only team_id & number of rounds won by them. So all data exist in Score table — TalESid, Apr 27 '22 at 07:34
@TalESid i am using the teams table in `WHERE` clause because i need just some teams, based on the tournament that they are playing. At the moment, my whole data is consisted of two teams in a single tournament, that played two rounds — poPaTheGuru, Apr 27 '22 at 07:35
Why does `teams` table have `tournament_id` as foreign key? It seems bad design as one team can participate in multiple tournaments and one tournament will have multiple team, so it is `n <-> n` mapping here. — vish4071, Apr 27 '22 at 07:46
@vish4071, actually a team can be only in one tournament. Because a team is created of two players, and the id of a team is a generated uuid, so no matter who the players, that exact team will only be in one tournament — poPaTheGuru, Apr 27 '22 at 07:46
Ok...so if the same 2-player team participates in another tournament, will you create a new team? I'm not sure what is your specific use case, but this intuitively doesn't seem right. But its alright if it suits your purpose. Also, this is a little off topic here, so nvm. Just think about it, maybe helpful in your project design :) — vish4071, Apr 27 '22 at 07:50
@vish4071, yes, in every tournament there will be new teams, because you can also play as a single player (that's how the app is thought) and I found it easier (i guess) to have new teams for every tournament, than to search if that players already created a team in the past and use it again — poPaTheGuru, Apr 27 '22 at 07:51

D-Shih · Accepted Answer · 2022-04-27T07:54:48.403

5

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

edited Apr 27 '22 at 07:54

answered Apr 27 '22 at 07:50

D-Shih

44,943
6
31
51

2

Yes, it worked, there was my mistake, that I tried to nest these aggregated functions, but it was supposed to be with sub-queries. Thank you for your time and understanding! (I have tried on my data and it's working) – poPaTheGuru Apr 27 '22 at 07:54
you beat me to the punch! also in his original question, he used count(max()) as far as I know you are not able to use those two together right? – Demeteor Apr 27 '22 at 08:02
I do have a question, for my knowledge, what if I want to get also the other teams (from teams table) in this query, but with score 0 if they didn't played yet? – poPaTheGuru Apr 27 '22 at 09:06
@poPaTheGuru I think you should open another question about this. also having 0 as the indicator if the team played a game or not is not the best choice. What if the team just scored 0? or even in your original question what if both teams had the score ? can they not have a tie ? is it impossible ? – Demeteor Apr 27 '22 at 09:52
@Demeteor, it's just for my knowledge, i don't know if a new question is needed. A tie might be possible one day, but it's like if you didn't played a round (game) yet, you have 0 points by default. In original question, in `Rounds table` are just the teams that have already played, but in the `Teams table` there are more teams, and initially there is not a single round played, so everyone should have 0 points, and in time as the games are played, the points increase. (if you don't get me, i mean that in the expected result there should be all the teams from that tournament) – poPaTheGuru Apr 27 '22 at 09:57

Select COUNT of MAX in MySQL

1 Answers1