I need to grab the max value of all of the data in a table grabbing the max total for each client. Example.
T1 id total clientid 1 100 111 2 200 111 3 100 112 4 150 112
I need a query that will grab the highest total per clientid (200, and 150) then average them. My first inclination is to do a subselect
select avg(total) from t1 where t1.total=(select max(total) from t1 t2 where t2.clientid=t1.clientid)
CREATE TEMPORARY TABLE tmp SELECT MAX(total) AS total FROM T1 GROUP BY clientid;
SELECT AVG(total) FROM tmp;
But can't use subselects with mysql....how do I do it??
Sure you can... with the right version.
---John Holmes...
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
