MySQL - group by on groupped result

Question

Not sure how to best title the question.

I have a table that records player's wins. I need to query for how many players have how many wins.

Here's an example DB: https://www.db-fiddle.com/f/gCEKmtPQr7nvhiZDyexn9C/0 Here's the expected result:

wins, players_count
1, 1
2, 3

And I'm a bit lost. I can get number of wins per player easily (group by user_id, where status='win' and select count on it), but how do I group those results into my expected result?

Thanks!

Answer 1

• You will need to use two-level Select queries with Group By. A sub-select query is also called as Derived Table.
• In the inner select query, get total wins per user.
• Now, in the outer select, Group By on the total wins, and count the users for them.

Try the following:

SELECT 
  dt.total_wins AS wins, 
  COUNT(*) AS players_count 
FROM 
(
  SELECT
    user_id, 
    COUNT(*) AS total_wins 
  FROM a 
  WHERE status = 'win' 
  GROUP BY user_id
) AS dt 
GROUP BY dt.total_wins 

Schema (MySQL v5.7)

CREATE TABLE a (status VARCHAR(255), user_id int);

INSERT INTO a (status, user_id)
VALUES ('win', 1),
('win', 2),
('win', 3),
('win', 2),
('win', 4),
('win', 4),
('win', 1),
('not win', 1);

---

Query Result

| wins | players_count |
| ---- | ------------- |
| 1    | 1             |
| 2    | 3             |

---

View on DB Fiddle

Answer 2

Here's an Example without CASE Statements:

After you got your wins_per_user simply count the user_ids, and group by wincount again:

SELECT
  wincount AS WinCount, count(user_id) as UserCount
FROM
  (SELECT 
   count(*) as wincount, user_id
  FROM a 
    where status='win'
  GROUP BY user_id) as wins_per_user
GROUP BY wincount

Results in

WinCount UserCount
1        1
2        3