[SOLVED] Help with a query please...

[Mr Chris]

Hi All,

 

I was wondering if someone could help me with a query.  I am trying to count appearances made for a football match.

 

Now as you can see below a football team has

 

- eleven players

- A maximum of Five subs and no minimum subs

- And three out of those five subs can be used

 

So from the below you can see that each player_id from player one to eleven has been given a used sub value of 1.

 

Then with my four substitutes (there was not a fifth named for this match) each used sub has a value of 1 as well.

 

So what I want to do is count each player_id for each report in my database where they have a used_sub value of 1 next to them.

 

How can I do this?

 

Thanks

 

[fenway]

Not sure I understand why you can't simply check that field directly...

[Mr Chris]

Thanks,

 

Basically I have many records in this database for match_id=1,  match_id=2 etc.

 

Then I want to produce an apperances table ie count how many times player_id=33 appears in this table or player_id=34 and count the number of used_sub's that player has made?

 

Thanks

 

Chris

[fenway]

What's the relationship between 33 and 34 in your example? Why not the subs of 33?

[Mr Chris]

Thanks, sorry if i'm not being clear.

 

Right there is no relationship between them.  Basically each player_id corresponds to a players name in the database in another table ie:

 

player_id=33  - Mike West

 

So basically in the above table (the jpg image) I'd like to loop through all the records and get each unique player_id.

 

Then i'd like to do a count on each row in the used_subs column to see how many appearances they have made throughout the season.

 

 

Is this possible?

 

Thanks

 

[fenway]

So SUM the used_subs for each player_id?

[Mr Chris]

Yes that's it thanks.what would the query be please?

[fenway]

SELECT player_id, SUM( used_subs ) FROM yourTable GROUP BY player_id

[Mr Chris]

Magic - thank you!