Complex Math Query

[LucidParody]

I have a fairly complex math query i was wondering if someone could help me out with. I'm trying to figure out how to query this.

I have one table like so (lets call it tourny):

id | powerrank

and another variable like so (let's call it atty):

id | tid | rank | ballots

the tid in the second table refers to the id in the first table. id in both are unique. now i can draw all that data at once, like so:

$query = "SELECT a.id as tournyName, a.powerrank, b.id, b.tid, b.rank, b.ballots FROM tourny a, atty b WHERE a.id = b.tid"

is there any way, in the query to run this formula:

First i'd want to run this formula on each row in the second table:

[(rank) + (1.5ballots)] [ log(powerrank)/3]

powerrank would correspond the matching id from the first table.

Then I'd like to take an average of all the matching id's in the atty table and then add how ever many occurances of that id there are in the atty table. so the final formula would look something like this:

AVERAGE{[(rank) + (1.5ballots)] [ log(powerrank)/3]} + num_of_rows_with_same_id

[LucidParody]

Progress!

i've got this much so far:

SELECT count(*) AS cnt, 
	AVG( ( a.rank + ( 1.5 * a.ballots) ) * 
		( LOG(b.powerrank) / 3 ) + count(*) ) 
		as total,
	a.name,
	a.school,
	a.tourny,
	a.rank,
	a.side,
	a.team,
	a.ballots,
	b.id,
	b.year,
	b.name,
	b.powerrank
	FROM xoops_ranks_atty a, xoops_ranks_tourny b
	WHERE a.tourny=b.id
	GROUP BY a.name
	ORDER BY total DESC
	

Can anyone show me how to use the value of 'cnt' where the second count(*) is?

TIA. 🙂