GROUP BY (nested group by required?) help
Hi, I am having problems figuring out how to display the correct information for a football player's stats/bio page. The database that I am drawing for collects data by each individual game played. (16 games per year, so there are 16 entries possible that state ('player_name', 'team_name', 'year', 'Week', 'games_played') with 'games_played' being a 1 for YES and a 0 for NO.
I GROUP BY 'year' and I collect a sum(games_played) to get that data, but team_name always says one team, even if the database shows the player was on numerous teams. So I need to determine a way to articulate that in the query return.
Any help or guidance would be much appreciated as I think at this point, I just need a second set of eyes...I'm drawing a blank.
**MYSQL CODE*************************
SELECT player_name,
team_name,
year,
week,
games_played
FROM player_stats
WHERE player_name = "Joe Montana"
GROUP BY year
ORDER BY year DESC;
************************************
The current return for "Joe Montana" is...
year team_name games_played
1990 SF 15
1991 SF 0
1992 SF 1
1993 SF 11
1994 SF 14
...but the reality is that "Joe Montana" played for "KC" in 1993 and 1994 (and the database is correct).
FORMAT1: Show Correct Team Name
year team_name games_played
1990 SF 15
1991 SF 0
1992 SF 1
1993 KC 11
1994 KC 14
or
FORMAT2: Group by Team Chronologically
team_name: SF
year games_played
1990 15
1991 0
1992 1
team_name: KC
year games_played
1993 11
1994 14