4

Consider the following table

enter image description here

I need to return a list in descending order with the count of the member_nr's who is appearing the most frequent in the table where tournament = 'EPL' AND ROUND = '12'

EXAMPLE

The script should return the following results:

enter image description here

I thought about the problem and my logic for the problem reads like this

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: GET the count(number of times) ^ABOVE^ member number appears in 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 very efficient to me, im looking for the shortest most efficient way to return the count of member numbers in table above, any ideas / suggestions are welcomed

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Timothy Coetzee
  • 5,626
  • 9
  • 34
  • 97
  • What about using *count* aggregate function? (see [12.16.1 GROUP BY (Aggregate) Functions](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count)) – agad Jun 26 '15 at 12:19

2 Answers2

3

Try something like this:

SELECT *, COUNT(*) AS `wins`
FROM `winners`
WHERE `tournament` = '$tournament'
  AND `round` = '$round'
GROUP BY `member_nr`
ORDER BY `wins` DESC
diggersworld
  • 12,770
  • 24
  • 84
  • 119
  • I can't remember whether COUNT gets applied before or after the GROUP BY but give it a go. – diggersworld Jun 26 '15 at 12:20
  • Thanks will do and give you feedback – Timothy Coetzee Jun 26 '15 at 12:23
  • You're missing `ORDER BY` to get the results in descending order by count. And you should use `count(*)` instead of `count(member_nr)`: http://stackoverflow.com/questions/2876909/count-and-countcolumn-name-whats-the-diff – Barmar Jun 26 '15 at 12:43
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
StevieG
  • 8,639
  • 23
  • 31
  • Why do you put `round` and `tournament` in `GROUP BY` when you're only selecting one of them with the `WHERE` clause? – Barmar Jun 26 '15 at 12:45
  • Force of habit.. Most SQL implementations require all non aggregated columns be included in the group by. Although I take your point that MySQL is an exception to this rule, so they're not really needed. – StevieG Jun 26 '15 at 12:50
  • They're also not really needed in the `SELECT`. – Barmar Jun 26 '15 at 12:51