Complex Site-Stats Query

[prc]

Hello,

I'm building statistics for a site and I want to output goal percentages.

What Im trying to achieve is a query to: group all unique 'ips', return there max 'goal', and add up ip's based on there goal.

http://www.l2image.com/images/fubvatyavqv6mpxonjvz.gif

This shows what I've done and what it currently returns. Hopefully better to visualize!

If you have any idea ill run through my data to see if it works.

Thanks!

[prc]

Is the only feasible way to do this is to run two queries?

I've got one working where I run one query to get unique "ip"s then
for each one run another query to get its max "goal" value. Its Real Hacky :/

[eirche]

what do you mean by "add up ip's based on there goal."?

[prc]

Get the max goal of each unique user (ip). Then,
add up the max goals against the goal number.
Should output like the table on the bottom right (of the image above.)

Is this understandable? I'm feeling it is to complex for one query!?

[mlk]

Do you necessarily need to run this in the query ?

Would it not be easier to run this in php ?

There's always the aggregate max function of mysql, but I haven't had my coffee so I can't really think straight: http://www.tizag.com/mysqlTutorial/mysqlmax.php

[prc]

It should be fine. Im running only one query now and doing a bit of sorting in PHP.

It was one of those things that seems it could be accomplished in a mysql query.


Edit: Found this is possible by combining two select queries in one

Thanks Again

[eirche]

your question is very confusing and not very informative. i am going to just guess what your table is like.

is your table like this? at least conceptually? it keeps track of all games played. only the maximum score of a player is important. you are interested in the occurrence of those maximum scores.

game
id, player, score
1, 127.0.0.1, 4
2, 127.0.0.2, 4
3, 127.0.0.2, 3
4, 127.0.0.8, 2
5, 127.0.0.1, 1



select count(`t`.`maxscore`) as `scorecount`, `t`.`maxscore`
from (select max(`score`) as `maxscore` from `game` group by `player`) as `t`
group by `t`.`maxscore`


gives
scorecount, maxscore
1, 2
2, 4

this is the best i can do. i would bow to him/her if one can do it with only one 'select'.

[prc]

Hey, well sorry. :/

You got me at explaining it clearly!

The code you provided is pretty much identical to what i came up with.

Thanks heaps anyway.

[eirche]

pretty much identical? what is your query like?