CODEWITHSUNDEEP
mysqlgroup-bydistinct
Get the Jaws of Life
Get the Jaws of Life

Reputation: 435

MySQL need a query using GROUP BY column_a to also ignore duplicates of column_b

I'm trying to get a report of the unique quantities of prizes won by users. ie. How players have won all 3 prizes, how many have won 2 prizes, etc..

+--------+--------+
| player | prize  |
+--------+--------+
|   1    |   1    |
+--------+--------+
|   1    |   1    |
+--------+--------+
|   1    |   2    |
+--------+--------+
|   1    |   3    |
+--------+--------+
|   2    |   1    |
+--------+--------+
|   2    |   2    |
+--------+--------+

The report I need should look like this:

+-----------+------------+
| prize_qty | player_qty |
+-----------+------------+
|      3    |      1     |
+-----------+------------+
|      2    |      1     |
+-----------+------------+

The Following code is close:

SELECT DISTINCT COUNT(*) as player_qty, prize_qty FROM 
(SELECT count( * ) AS prize_qty FROM `prizes` GROUP BY player)
as t1 GROUP BY player_qty

but it returns this:

+-----------+------------+
| prize_qty | player_qty |
+-----------+------------+
|      4    |      1     |
+-----------+------------+
|      2    |      1     |
+-----------+------------+

I need it to ignore that player #1 won prize #1 twice, but I'm not sure how further to illiminate duplicates.

Upvotes: 2

Views: 487

Answers (3)

pomel
pomel

Reputation: 410

Try this:

SELECT DISTINCT COUNT(player) as player_qty, prize_qty FROM 
(SELECT count( distinct prize ) AS prize_qty, player FROM `prizes` GROUP BY player)
as t1 GROUP BY prize_qty

Upvotes: 0

gbn
gbn

Reputation: 432331

You said

How players have won all 3 prizes, how many have won 2 prizes, etc..

and

I need it to ignore that player #1 won prize #1 twice

So shouldn't you count players per prize after removing the fact that a player won the same prize more than once?

SELECT COUNT(*) AS player_qty, prize AS prize_qty
FROM
  (SELECT DISTINCT prize, player FROM prizes) AS T1
GROUP BY prize

Upvotes: 3

chaos
chaos

Reputation: 124317

SELECT DISTINCT COUNT(*) as player_qty, prize_qty FROM 
(SELECT count( * ) AS prize_qty FROM `prizes` GROUP BY player, prize)
as t1 GROUP BY player_qty

Upvotes: 0

Related Questions