help with aggregate queries (sort of)

[mattgoody]

What im trying to do is select a bunch of stats froma table and also select a sum. what i want is to select the points for each player, and select a calculation thats like: sum(FGM)/individual FGM i wrote a query of how i think it might look, but its definetely not right. but i need the sum to only be of the top 150 records.

select PTS, (sum(FGM) from (select FGM from Jan07 ORDER BY MIN DESC LIMIT 0, 150) calc)/FGM FROM Jan07;

i dont even know if what i want to do is possible. if this is posible, i would be taking the sum of 6 different categories of the first 150 records, but they would not be one after another in the query. i.e.

select PTS, (sum(FGM) from (select FGM from Jan07 ORDER BY MIN DESC LIMIT 0, 150) calc)/FGM,
BLK, (sum(FTM) from (select FTM from Jan07 ORDER BY MIN DESC LIMIT 0, 150) calc)/FTM FROM Jan07;

Thanks for your help

-Matt

[chazzy]

Can you explain it clearly, to someone as if they had no idea what your app is doing?

[mattgoody]

ya sorry, i posted when i was tired and frustrated, my bad. what im doing is rating players based on their statistics (for fantasy sports etc). to calculate a field goal percentage score, however, i cannot just take the field goal percentage and then score it against the rest of the players, because in that instance, a player who only only took 2 shots, and makes them both is rated far too highly. so to calculate this category (field goal percentage) i need to use a formula based on the difference the players percentage is from the rest of the league. to do this my equation is:

(total field goals in the league + field goals made by specific player) / (total field goals attempted in the league + field goals attempted by said player)

the thing is, i need to only get the total field goals from the top 150 players in the league sorted descending by minutes played. what i posted earlier (the mysql queries) is pretty much the same idea (if i knew how to do that, i could do this) but i was trying to keep the formulas a little simpler. Jan07 is the table with the player stats up to january 7th.
Thanks, and sorry about the first post.

-Matt

[chazzy]

The only way I can think of to do this is via a subquery. you could also do an inner join that creates the appropriate total columns. i guess you would need to have sum(attempted) and sum(successful)

[mattgoody]

is there anyway to do something like:

SELECT sum(SELECT FGM from Jan07 ORDER BY MIN DESC LIMIT 0,150); ?

[chazzy]

what version of mysql are you using? (you're using mysql, right?)

[mattgoody]

yeah, sorry im using mysql 4.1.21

[chazzy]

I'm not sure why you don't just use a join....

This query works:

select sum(id) as a, sum(id) as b, sum(id) as c from users order by name desc limit 0,150;

Obviously you would need to substitute your own columns, but if you join what you're generating with this, it should work.

select u.name, u.id-t.a as diff, t.b FROM users u, (select sum(id) as a, sum(id) as b, sum(id) as c from users order by name desc limit 0,150) t;