Hi, i have this query:


SELECT count( a.name ) AS cnt,
AVG( ( a.rank + ( 1.5 * a.ballots ) ) *
( ( LOG(b.powerrank) / 3 ) ) + cnt )
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 atty a, tourny b
WHERE a.tourny=b.id
GROUP BY a.name
ORDER BY total DESC


I want to be able to use the value of cnt in that AVG formula, but it's telling me I can't. how would i do this?

tia.

SELECT AVG( ( a.rank + ( 1.5 * a.ballots ) ) *
( ( LOG(b.powerrank) / 3 ) ) + count( a.name ) )
as total....

I've tried that and mysql says it's an invalid use of the group function.

Because it would need to go through the table twice -- once to gather the count, then again to deal with the average. However, increasing all points in an average by a constant will simply increase the average by that constant, so this should accomplish the same goal:

SELECT count( a.name ) AS cnt,
AVG( ( a.rank + ( 1.5 * a.ballots ) ) *
( ( LOG(b.powerrank) / 3 ) ) )
+ count( a.name ) as total,