CODEWITHSUNDEEP
phpmysqlsqlmysqliphpmyadmin
Tim C
Tim C

Reputation: 5714

Count the number of times each members name appears in a table with a condition

Consider the following table:

enter image description here

Im looking for a query that will:

- Count the number of times each members name appears in the table where tournament= "EPL" AND round ="12"

For this EXAMPLE the query should return:

Andrew = 3 Wins

Joseph = 2 wins

John = 1 win

Martin = 1 Win

I thought about just using a simple count query as in:

Select count(winning_member) as nrWins WHERE tournament="EPL" and round="12" and winning_member = '$mem_name'

But this will result in me having to loop over ALL users, which is not an option.

Im completely stuck on this problem...Any input will be much appreciated

Upvotes: 2

Views: 73

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94642

If you actually want the exact output you mentioned you could try this

SELECT CONCAT(winning_member, ' = ', count(*), ' Wins')  
WHERE tournament="EPL" 
AND round="12" 
GROUP BY winning_member;

Upvotes: 1

Saumini Navaratnam
Saumini Navaratnam

Reputation: 8850

Try below query

SELECT count(*) as nrWins, winning_member WHERE tournament="EPL" and round="12" GROUP BY winning_member;

Upvotes: 0

Related Questions