# question

## 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
tables.png (30.4 KiB)

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

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 ;