Query Library

Casey Trauer submitted this query on: January 07, 2011 7:25pm EST
TITLE Get NBA season stats for players on a team
SPORTS Basketball
QUERY SELECT dn_team.first_name, dn_player.first_name, dn_player.last_name, p.person_key, off.field_goals_made FG, off.field_goals_attempted FGA, off.three_pointers_made 3G, off.three_pointers_attempted 3GA, free_throws_made FT, free_throws_attempted FTA, off.points_scored_total PTS, off.assists_total AST, off.turnovers_total 'TO'

FROM sub_seasons AS ss
JOIN seasons ON ss.season_id = seasons.id
JOIN stats AS s ON (s.stat_coverage_type = 'sub_seasons' AND s.stat_coverage_id=ss.id)
JOIN basketball_offensive_stats off ON (s.stat_repository_id = off.id AND s.stat_repository_type = 'basketball_offensive_stats')
JOIN display_names dn_player ON (dn_player.entity_id = s.stat_holder_id AND dn_player.entity_type = 'persons' AND s.stat_holder_type = 'persons')
JOIN display_names dn_team ON (dn_team.entity_id = s.stat_membership_id AND dn_team.entity_type = 'teams' AND s.stat_membership_type = 'teams')
JOIN persons p ON s.stat_holder_id = p.id
JOIN teams t ON s.stat_membership_id = t.id
JOIN publishers pub ON seasons.publisher_id = pub.id

WHERE pub.publisher_key = 'sportsnetwork.com'
AND ss.sub_season_key = '2010_season_regular'
AND t.team_key = 'l.nba.com-t.1'
ORDER BY off.points_scored_total+1 DESC;
DESCRIPTION This query returns offensive season stats for Boston Celtics players during the 2010 regular season.
DBs TESTED MySQL
ADDITIONAL NOTES
Comments
on January 25, 2011 5:52pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn_team.first_name, dn_player.first_name, dn_player.last_name, p.person_key, core.events_played, off.field_goals_made FG, off.field_goals_attempted FGA, off.three_pointers_made 3G, off.three_pointers_attempted 3GA, free_throws_made FT, free_throws_attempted FTA, off.points_scored_total PTS, off.assists_total AST, off.turnovers_total 'TO'

FROM sub_seasons AS ss
JOIN seasons ON ss.season_id = seasons.id
JOIN stats AS s ON (s.stat_coverage_type = 'sub_seasons' AND s.stat_coverage_id=ss.id)
JOIN basketball_offensive_stats off ON (s.stat_repository_id = off.id AND s.stat_repository_type = 'basketball_offensive_stats')
JOIN stats AS s2 ON (s2.stat_coverage_type = 'sub_seasons' AND s2.stat_coverage_id=ss.id)
JOIN core_stats AS core ON (s2.stat_repository_id = core.id AND s2.stat_repository_type = 'core_stats')
JOIN display_names dn_player ON (dn_player.entity_id = s.stat_holder_id AND dn_player.entity_type = 'persons' AND s.stat_holder_type = 'persons')
JOIN display_names dn_team ON (dn_team.entity_id = s.stat_membership_id AND dn_team.entity_type = 'teams' AND s.stat_membership_type = 'teams')
JOIN persons p ON s.stat_holder_id = p.id
JOIN teams t ON s.stat_membership_id = t.id
JOIN publishers pub ON seasons.publisher_id = pub.id

WHERE pub.publisher_key = 'sportsnetwork.com'
AND ss.sub_season_key = '2010_season_regular'
AND t.team_key = 'l.nba.com-t.1'
AND s.stat_holder_id = s2.stat_holder_id
ORDER BY off.points_scored_total+1 DESC;

"Adds column for games played, which is stored in the core_stats table."