sql Server: Rank by sum of points and order by ranking

Question

I have a game table with these fields:

ID    Name       Email      Points
----------------------------------
1     John     [email protected]    120
2     Test     [email protected]     100
3     John     [email protected]    80
4     Bob      [email protected]     50
5     John     [email protected]    80

I want to group them by email (email Identifies that both players are the same no matter that row 2 and 4 have different names) and have also sum of points and the last entered name in the results and rank them with the heighest sum of points to the lowest

the Result I want from the sample table is:

Ranking     Name       Points   Games_Played      Average_Points 
------------------------------------------------------------------------------------------
 1          John        200         2                100
 2          Bob         150         2                75
 3          John        80          1                80

I could achieve getting ranking, sum of points, and average points but getting the last entered name I think need joining with the same table again and it seems a little wrong.

Any ideas how to do this?

Answer 1

Displaying the Name and grouping be email will cause to use e.g. MIN(Name) and lead to duplicate names.

Select Rank() over (order by Points desc) as Rank
,Name,Points,Games_Played,Average_Points
from
(
Select Min(Name) as Name,Email,Sum(Points) as Points
,Count(*) as Games_Played,AVG(Points) as Average_Points
From @a Group by Email
) a 
order by Rank

SQLFiddle

in the Fiddle are two commented lines you should uncomment to see the behavior on identical results.

Answer 2

You can use Ranking Functions from SQL-Server 2005 upwards:

WITH Points 
     AS (SELECT Sum_Points = Sum(points) OVER ( 
                                 partition BY email), 
                Games_Played = Count(ID) OVER ( 
                                 partition BY email), 
                Average_Points = AVG(Points) OVER ( 
                                 partition BY email), 
                Rank = DENSE_RANK()  OVER ( 
                              Partition BY email Order By Points DESC),
                * 
         FROM   dbo.Game)
SELECT Ranking=DENSE_RANK()OVER(ORDER BY Sum_Points DESC), 
       Name, 
       Points=Sum_Points, 
       Games_Played,
       Average_Points
FROM   Points 
WHERE Rank = 1
Order By Sum_Points DESC;

DEMO

Note that the result is different since i'm showing the row with the highest point in case that the email is not unique, so "Test" instead of "Bob".