MySQL Forums
Forum List  »  General

GROUP BY (nested group by required?) help
Posted by: Mike Cossaboom
Date: June 22, 2013 08:05AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.