php mysql help

[Adam79]

I am looking for some help, our hockey team has 20 years of stats that i need to sort.

I need to figure out most goals, assists and points by a player in all the years that the player has played.

i was wondering what the easiest way to go about this would be?


Currently i have a database field for (id, name, gp, goals, assists, pim, year)


my problem is i am inputting all the info individually by year so a player could play 5 years and some could play 1 but i want to show the combined totals for each player 

example player1 played 5 years i have all his stats in the database i want to combine all the stats from the 5 years that players played and show it on a single page like total amount of goals, assists pts, gp(games played) pim like so
 

Name  - Player1

 

GamesPlayed - 123,

 

Goals - 93,

 

Assists - 91

 

Pts - 184,

 

Pim - 239

any help is greatly appreciated

thanks

[aysiu]

So is there just one table with all those fields?

 

And if a player has played five years, that one player has five separate records in that one table?

[denno020]

Assuming that the field 'games played' represents that number of games that player with name 'name' played in the given year, set in the 'year' field, then you could do a simple SELECT COUNT() for each field that you need tallies for. This would obviously mean that for each player that's played more than one year, they'll have their name in the database more than one time. Usually the best idea would be to have the name in there only once, by normalizing your databases (I think it's called), to separate data out into related tables..

[Barand]

SELECT

      name

    , SUM(gp) as played

    , SUM(goals) as goals

    , SUM(assists)as assists

    , SUM(pts) as points

    , SUM(pim) as pim

FROM stats

GROUP BY name

 

Edited December 1, 2013 by Barand

[Adam79]

Thanks for the help its perfect