question

raadee avatar image
raadee asked

T-SQL Create Query - Compare and Calculate

I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points". Basically I got two tables. ![alt text][1] Source table: This is a table with statistical per game averages for a category (category = column). Destination table or result set: This is a points table. If I have the highest average in a category I get maximum points which is same as number of teams. So if I have 10 teams and I have the highest average in 3PM category I get 10 points, and so on down to 1 point for the worst team in the category. Last column is a total of all points per team. The help I need: I would like to create a query that compares every teams value in a given column/category in the source table, ranks the results and gives the right amount of points. When finished it adds up the points per team to the Total PTS table. Appreciate it if someone would help me or show me the path to fantasy glory. [1]: /storage/temp/1935-tables.png
t-sqlsql serverrank
tables.png (30.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
There is a RANK function that should help a lot. WITH IndividualRanks AS ( SELECT Team , Rank3PM = RANK() OVER (ORDER BY [3PM]) , RankREB = RANK() OVER (ORDER BY [REB]) , RankAST = RANK() OVER (ORDER BY [AST]) , RankSTL = RANK() OVER (ORDER BY [STL]) , RankBLK = RANK() OVER (ORDER BY [BLK]) , RankPTS = RANK() OVER (ORDER BY [PTS]) FROM SourceTable ) SELECT Team , Rank3PM, RankREB, RankAST , RankSTL, RankBLK, RankPTS , TotalPoints = Rank3PM + RankREB + RankAST + RankSTL + RankBLK + RankPTS ORDER BY TotalPoints DESC -- show top ranked team first ;
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@radee @Tom Staab's answer gives you a complete solution but keep in mind that it will assign the same number to 2 teams if they have the same value in the column. For example, suppose there are 2 top teams with the same value in REB per game column. In this case both of these teams will have the rank = 1 while the next team will get the rank = 3 (not 2, 2 will be skipped). If you want the number to never skip then you can use dense\_rank in place of rank and if you need to have some other rule then you can adjust the statement in the answer accordingly.
1 Like 1 ·
Many thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.