Ordering by average

Anon

I run a site based on Tottenham Hotspur, an English football team. For every match, I input all the info... who played, who scored etc, who was booked, ratings for each player.

What I want is a table to be created that has all this info for every player in one handy place. So, it would look something like this:

player goals apps av. rating bookings
Poyet 5 27 8.45 3
Rebrov 11 39 7.24 0
Sheringham 27 41 9.24 6

and so on....

Now, the info is stored thus:

table: fixtures_goals
rebrov 27 mins
poyet 39 mins .........

table: fixtures_apps
poyet
sheringham
rebrov
carr
sheringham.......

table: ratings
2001-08-18 sheringham 8
2001-08-18 poyet 7
2001-08-18 rebrov 9.........

table: fixtures_bookings
poyet
sheringham
poyet.........

What I'm trying to do is very difficult to explain, but I hope you get the picture.

What I'm having a problem with is the average rating. I can't work out how to create the average in the mySQL statement.

Any help whatsoever would be greatly appreciated.

jacinthe

I'm not sure I understood everything but for the average... try this out

select player, avg(col_name) average from ratings group by player order by average

hope that'll help you a bit...

Jacinthe

Anon

I don't know if this is what your looking for by your post but would something like this work?

SELECT player_name, AVG(player_rating) FROM table_name GROUP BY player_name

Anon

Yes, that helps, but I've run into another problem...

I want to combine average ratings, goals scored, appearances etc. into one table.

So, I want a list of players, got from a table called 'players', with the number of goals scored, appearances made and their average rating next to each name.

How do I combine these statistics?

Once again, I haven't explained myself very well ;o)

Anon

You have to link all your table together.

Like, for example, you have two tables :

Student
ID, Name, City, Teacher_number

Teacher
ID, Name, Course

so youll have to do something like this :

select * from student s, teacher t where s.teacher_number = t.ID

see what i mean? that way you link your data together and then you can use them.

So for your case maybe it could look like that :

select p.field1, g.field2, a.field3, avg(col_name) average from players p, goals g, appearances a where p.player_name = g.player_name and p.player_name = a.player_name

well, i made it fast but it could look like that...

Good luck 🙂

Jacinthe