Unanswered: sorting and summing rank indexes based on sorting from another derived field
I am pretty new to MySQL (or SQL of any sort) but I have got quite deep into a project I am working on with lots of late nights and struggling through. I have finally hit a wall with this problem though - i would love some help with syntax and structure of this query.
I have a table of race results containing individual races results over a whole race series. The individual races are ranked based on a derived field. What I need to do is sum the ranking for each race and present in a table with each race a vertical column with a total rank at the far right. My table has the following fields;
My derived column is (results.Mins*60 + results.Secs)*1000/classes.PYN/results.Nlaps as cTime
My query for evaluating each race is;
(results.Mins*60 + results.Secs)*1000/classes.PYN/results.Nlaps as cTime,
results.Mins, results.Secs, results.Nlaps,
m1.FirstName as Hfn, m1.LastName as Hln,
m2.FirstName as Cfn, m2.LastName as Cln,
classes.Class, boats.SailNo, combos.ComboID,
results.RaceID, results.Code, results.ResultID
FROM members m1, members m2, classes, boats, combos, results
order by cTime;
I reference other tables to get all race info I need. I am stuck regarding how I make a query that does the above for each race in the series AND sums the individual rankings across all races to provide a series ranking. I want a table of participants with each race ranking in a new column, sorted by the sum of the individual ranks.
I am using PHP to process and create HTML output, but it seems best to get MySQL to do the work rather than PHP. I don't even know how I'd do it in PHP (i'm learning that too!)
Can anyone help me? I've tried to include the important bits but keep irrelevant data to a minimum.
Many thanks in advance,
PS I apologise if this post doesn't follow the forum guidelines - I'm new to this too!