Return list count_number in table

Question

Consider the following table

I need to return the list in descending order with the count of member_nr, which is most often found in the tablewhere tournament = 'EPL' AND ROUND = '12'

Example

The script should return the following results:

I thought about the problem and my logic so that the problem reads as follows

STEP1: GET member_nr one by one

 $sql = "SELECT DISTINCT * 
        FROM winners 
        WHERE tournament='$tour' AND round='$round'";
LOOP(){ //get 1 member number
$mem_nr = ['mem_nr']; //assign mem_nr to variable

STEP2: START account (number of times) ^ The number of ABOVE elements ^ appears in the table

  "$sql="SELECT *, count(member_nr) as nrWins 
   FROM winners 
   where member_nr ='$memNr' and tournament='$tournament' AND     round='$round'";"
 LOOP(){//get count

STEP 3: DISPLAY DATA

echo $row=['nrWins'] //Display Number Wins
echo $memNr
   }//END LOOP
 }//END LOOP

My problem:

The above does not seem to me very effective, I am looking for the shortest most effective way to return the number of members in the table above, any ideas / suggestions are welcome

Answer 1

- :

SELECT *, COUNT(*) AS `wins`
FROM `winners`
WHERE `tournament` = '$tournament'
  AND `round` = '$round'
GROUP BY `member_nr`
ORDER BY `wins` DESC

Answer 2

select tournament,round,member_nr,count(*)
from table
where tournament = 'EPL'
and round = 12
group by tournament,round,member_nr
order by count(*) desc