COUNT query

[xtrad]

Hi,

 

I have a table, 'LeagueResults1', with the following five columns:

 

resultID, machine, player, score, meet

 

I am trying to write a query to produce a list of how many high-scores each player has.  I simply want to show their name and the number of high-scores they have.

 

I currently produce a list showing the high-score for every machine with the corresponding player and meet next to it:

 

$query = "SELECT f.machine, f.player, f.score, f.meet FROM
(SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine)
AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine";

 

I tried to adapt this query to count the number of high-scores each player has, but have had no success.

 

Any suggestions would be much appreciated.

[fenway]

Could you post sample output from the query, and the desired output as well?

[xtrad]

OK, this is an example of what the above query outputs:

 

[/img]

 

Basically it's an alphabetical list of the machines showing the high-score, the player who scored it, and the meet it was scored at.  The full list includes around 130 machines.

 

So, you can find out how many high-scores you have by counting how many times your name appears on the list.  But i wanted to make it easier by writing another query that counts how many high-scores each player has and produces a simple list like this (ordered by the biggest number of high-scores):

 

Player, Number of high-scores

player_a, 10

player_b, 9

player_c, 5

etc

 

I have tried adapting the query i used to get the highscores, but without success.

 

[attachment deleted by admin]

[fenway]

So you don't care which machine it's own?

[xtrad]

A list showing the player's name and the number of high-scores they have already exists but it's just in html and is updated manually.  I was simply trying to recreate that list automatically using an SQL query.

 

But now you mention it, i think it would be a lot better to show the player's name and all the machines they have high-scores on, instead of just a number for how many high-scores they have.

[fenway]

Clearly, I'm lost.

[xtrad]

Sorry about that!  I'll try to clarify...

 

The image i attached to my second post in this thread shows the output of the query from my first post.  It's basically a list of all machines, in alphabetical order, showing the high-score on each machine, who scored it and when it was scored.

 

That list is great when you wonder who has the high-score on a particular machine.

 

But, if a player wonders how many machines he has the high-score on, he has to look down that entire list (about 130 rows) and count how many times he sees his name.

 

So, i wanted to create another, separate list which has all the players' names with a number next to them representing the number of machines they have the high-score on.  Preferably with the player with most high-scores at the top.  Or perhaps instead of just a number next to each player, i could actually list all the machines each player has the high-score on.

 

Anyway, it's writing that query i am stuck with.  As the query in my first post on this thread already finds all the high-scores i tried to adapt it to count the number of high-scores each player has but have not succeeded.