Moving Beyond the Row: The OVER Keyword4:36 with Danielle Kiowski
The OVER keyword lets you transform an aggregate function into a window function. This lets an aggregate function see, and access, all of the results of a query.
OVER — keyword used to create window functions. Follow with empty parentheses to access the entire result set.
SELECT PLAYER_NAME, PLAYER_RANKING, PLAYER_TEAM, COUNT(MATCH_ID) as MATCHES_PLAYED, SUM(CHECKMATE) as MATCHES_WON, SUM(CHECKMATE)/COUNT(MATCH_ID) as WIN_RATE, AVG(PLAYER_RANKING) OVER () as AVG_RANKING, PLAYER_RANKING - AVG(PLAYER_RANKING) OVER () as DIFF_FROM_AVERAGE, SUM(SUM(CHECKMATE)) OVER () as TOTAL_MATCHES, SUM(MAX(CHECKMATE)) OVER () as PLAYERS_WITH_WINS, MAX(SUM(CHECKMATE)) OVER () as HIGHEST_WINS FROM treehouse.chess_data_matches GROUP BY PLAYER_NAME, PLAYER_RANKING,PLAYER_TEAM
You need to sign up for Treehouse in order to download course files.Sign up