Ranking problem

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

If any of you are familiar with fantasy baseball,
teams "pick" real players and their stats. I have a
table of hitters. I have a query that filters records by
the team that picked them (there are 12 teams) and
calculates totals for each team based on the players
picked's stats. I would like to rank the 12 teams in
each stat and end up with a number for the rank (i.e. the
number 1 for 1st in a category, 2 for 2nd, etc.). In the
end, I want to add up the numbers each team gets from the
rankings and get an overall ranking for each. Any help
is appreciated. Below is the SQL for the query I have so
far. I'm primarily interested in the first five
categories after "Drafted," anything else is a bonus.
Thanks.

SELECT Sheet1.Drafted, (Sum([Sheet1.Hit])/Sum
([Sheet1.AB])) AS TotalBA, Sum(Sheet1.Run) AS SumOfRun,
Sum(Sheet1.HR) AS SumOfHR, Sum(Sheet1.RBI) AS SumOfRBI,
Sum(Sheet1.SB) AS SumOfSB, Sum(Sheet1.G) AS SumOfG, Sum
(Sheet1.AB) AS SumOfAB, (Sum([Sheet1.AB])/Sum
([Sheet1.HR])) AS [TotalHR/AB], ((Sum([Sheet1.Hit])+Sum
([Sheet1.BB])+Sum([Sheet1.HBP]))/(Sum([Sheet1.AB])+Sum
([Sheet1.BB])+Sum([Sheet1.HBP])+Sum([Sheet1.SF]))) AS
TotalOBP, (Sum([Sheet1.TB])/Sum([Sheet1.AB])) AS
TotalSLG, ([TotalOBP]+[TotalSLG]) AS TotalOPS, Sum
(Sheet1.TB) AS SumOfTB, Sum(Sheet1.Hit) AS SumOfHit, Sum
(Sheet1.[2B]) AS SumOf2B, Sum(Sheet1.[3B]) AS SumOf3B, Sum
(Sheet1.BB) AS SumOfBB, Sum(Sheet1.SO) AS SumOfSO, Sum
(Sheet1.CS) AS SumOfCS, Sum(Sheet1.GIDP) AS SumOfGIDP, Sum
(Sheet1.ERR) AS SumOfERR, Sum(Sheet1.HBP) AS SumOfHBP, Sum
(Sheet1.SF) AS SumOfSF, Sum(Sheet1.SH) AS SumOfSH, Sum
(Sheet1.[TB formula]) AS [SumOfTB formula]
FROM Sheet1
GROUP BY Sheet1.Drafted
HAVING (((Sheet1.Drafted) Is Not Null));
 
Hi,


If you have a table Table1 (or a query) with the field theValue to rank
the records among themselves, then you can do something like:

---------------------------------------
SELECT a.primaryKey,
(1+COUNT(*)) As the Rank

FROM Table1 As a LEFT JOIN Table1 As b
ON a.TheValue > b.TheValue

GROUP BY a.primaryKey
----------------------------------------

If you have to rank within sub-group, like, "by country", then try:

----------------------------------------
SELECT a.primaryKey,
LAST(a.country),
(1+COUNT(*)) As the Rank

FROM Table1 As a LEFT JOIN Table1 As b
ON (a.country=b.country)
AND
(a.TheValue > b.TheValue)

GROUP BY a.primaryKey
----------------------------------------



Your exact formulation would differ, but I kept the example as simple as
possible, to illustrate the concept without useless extra complexity, so
that you can apply it by yourself to your specific case.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top