I am trying to run a query with using a join and my issue is it is giving me an error saying their is not column named "MAX" so I am hoping someone can provide a better why to right this query


SELECT teams.rep, teams.sup_manid, MTD_AHT.Groupwiseid, teams.MAX( sup_manid ) , teams.Max( assign_date ), MTD_AHT.avg(AHT) AS AHT FROM teams, MTD_AHT WHERE teams.sup_manid = 'jdoe' AND MONTH(assign_date) <='7' AND teams.rep = MTD_AHT.Groupwiseid AND MTD_AHT.MTD=1 GROUP BY rep


what the query is suppose to do is provide an average of a AHT column for only people that are assigned to the person named jdoe for the month of july .

I also tried writing this query this way which did not work

//lets get the average for each column
$gn2 = mysql_query("SELECT rep, MAX( sup_manid ) , Max( assign_date ) FROM teams WHERE sup_manid = 'jdoe' AND MONTH(assign_date) <='7' GROUP BY rep");
while($nr2 = mysql_fetch_array($gn2))
{
extract($nr2);
//avg aht
$getaht2 = mysql_query("SELECT avg(AHT) AS AHT FROM MTD_AHT where GroupwiseID='$rep' AND MONTH(Date) ='7' AND MTD='1'");
$goalrow2 = mysql_fetch_array($getaht2);
$ahtavg = floor($goalrow2['AHT']);
}
echo "$ahtavg";



So basically I have a teams table that holds which people are assigned to which supervisor/manager then I have a table called MTD_AHT which stores a score for a specific performence measure. So what I need to do is for any given month get a which team members where assigned to a specific supervisor/manager and then get the total average score for the above quried performence measure

Your query has bad syntax from what it looks like.

teams.MAX(sup_manid) should be MAX(teams.sub_manid)

etc