I have a table with the columns id, player1, and player2. The ID's for a player can be in either of the two columns. I need to find all the unique ID's in column player1 and column player2 combined. Then I need to also find the amount of times each player appears in each column. Finally I want to order it by the ratio of column player1 / column player2.
For instance, I have the following values in the table.
1 103 101
2 103 111
3 232 103
4 223 111
My query would return..
Player 223: 1/0
Player 232: 1/0
Player 103: 2/1
Player 101: 0/1
Player 111: 0/2
I know for the unique IDs I can do something like this
select
(SELECT group_concat(DISTINCT player1) FROM table) as player1,
(SELECT group_concat(DISTINCT player2) FROM table) as player2
and I know for the order I can do something like this
ORDER BY player1 / player2 DESC
I'm really just having a hard time figuring out what to do once I get the unique ID and then try to get the ratios outputed and sorted
Try this
select player1,player2, CONCAT('Player',IF( (select count(DT.player1) from table as DT where MT.player1 = DT.player1) > 0, player1,player2),':', (select count(DT.player1) from table as DT where MT.player1 = DT.player1), ' / ', (select count(DT.player2) from table as DT where MT.player2 = DT.player2) )
from table MT
where player1 != player2
group by palyer1,player2
order by (player1/IF(player2 is not null and player2 != 0,player2,1) desc
I hope it helps you
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments