SQL Help Finding Max total value
I need help finding the total开发者_如何学运维 points of a player and Team name based on this table:
Tbl.STATS [Match_ID,Player_ID,Points_Scored,Team_Name]
Any advice or help would be much appreciated!
All players on all teams
select player_id, team_name, sum(points_scored) sum_score
from stats
group by player_id, team_name
order by sum_score desc
A player and team name (for example, Bob playing for Eagles, so not considering when Bob played for Sharks)
select sum(points_scored) sum_score
from stats
where player_id = 1 and team_name = 'Eagles'
But the question asks something else again, so going by the question, the MAX total score:
select max(sum_score) MaxTotalScore
from
(
select player_id, team_name, sum(points_scored) sum_score
from stats
group by player_id, team_name
) X
SELECT player_id, team_name, sum(points_scored) FROM STATS Group by player_id, team_name
"I'm looking to find the player that scored the most total points and what team the player is on."
I don't normally dabble in the Oracle tag but I believe this should work!
WITH T AS
(
SELECT Player_ID,
Team_Name,
SUM(Points_Scored) OVER (PARTITION BY Player_ID) AS Points_Total
FROM Tbl.STATS
), T2 AS
(
SELECT Player_ID,
Team_Name,
Points_Total,
RANK() OVER (ORDER BY Points_Total DESC) AS RN
FROM T
)
SELECT Player_ID,
Team_Name,
Points_Total
FROM T2
WHERE RN=1
精彩评论