Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Grouping by multiple columns is not working

I have a simple table consisting of data like such:

Tournament   Team   Round   Placement
41           1      1       1
72           6      2       1
16           8      2       5
33           2      1       4
36           4      1       2
90           1      2       5
33           3      1       1
10           7      1       1
41           1      2       1
72           3      2       2
16           4      2       3
33           1      1       1
72           2      2       3

I want to find the number of times a team has placed 1st in a unique tournament, regardless of round. My query is set up like this:

SELECT Team, COUNT(*) from table_results WHERE Team='1' AND Placement='1' GROUP BY Tournament, Team

In the example table, we see that Team 1 has results in 4 rows, but only 3 of them are unique tournaments. The result I get from the query does group the duplicate entries together, but it does not group the Teams together. In other words, I get this:

Team   COUNT(*)
1      2
1      1
1      1

But what I really want is this:

Team   COUNT(*)
1      3

All of the previous questions I have read have indicated that if you GROUP BY two columns (separated by a comma), it will group the results by the first column and then the second. In my case it is grouping by the first column correctly but it is then not grouping those rows together and counting them to give me a final result. What am I doing incorrectly?

Thank you very much in advance.

Tags:

4

Answers


  1. 0

    Try the following Query
    SELECT Team ,COUNT(Tournament) FROM table_results WHERE Team = '1' AND Placement = '1' GROUP BY Team

    Login or Signup to reply.
  2. 0

    Filter the data to only include rows where the team has placed 1st, then count the distinct tournaments for each team.

    SELECT Team, COUNT(DISTINCT Tournament) as UniqueTournaments
FROM (
    SELECT Team, Tournament
    FROM table_results
    WHERE Placement = '1'
) as FilteredResults
GROUP BY Team;
    Login or Signup to reply.
  3. 0

    enter image description here

    Why do you need group by Tournament, Team? The result that you want only need group by team.

    SELECT Team, COUNT(*) from TournamentResults WHERE Team='1' AND Placement='1' GROUP BY Team

    Login or Signup to reply.
  4. 0

    The issue you’re encountering is due to the way you’ve structured your GROUP BY clause. When you GROUP BY Tournament, Team, the query groups the results first by Tournament and then by Team within each tournament. This means you’ll get a separate count for each combination of Tournament and Team, which is not what you’re looking for since you want to count the number of unique tournaments where a team placed 1st, regardless of the round.

    To achieve the desired result, you should first create a subquery that selects distinct tournaments where the team placed 1st. Then, you count the number of entries in this subquery for each team. Here’s how you can adjust your query:

    SELECT Team, COUNT(DISTINCT Tournament) AS count
FROM table_results
WHERE Placement = '1' AND Team = '1'
GROUP BY Team
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search