UNION / GROUP BY question

programming
1

ok I’ve got a database that has 4 fields for salesperson and depending on your commission percentage you are slotted into one of these fields. Also i have 4 commission rate fields that are associated with the salesperson fields. What I need to do is search all 4 fields and find the same salesperson and group that salespersons total sales into one number then I need to multiply that by the average commission and get their total sales for the last month. Its like a “Leader Board” but i seem to be having some trouble when i try to “GROUP BY” I’m hopeful someone can help.

Here is the code i’me currently using keep in mind this does not have the code to only display the last month built into it yet:

<?php
$query = "(SELECT sales_person_1, SUM(system_size), AVG(commission1) FROM pv_jobs GROUP BY sales_person_1) 
UNION (SELECT sales_person_2, SUM(system_size), AVG(commission2) FROM pv_jobs GROUP BY sales_person_2) 
UNION (SELECT sales_person_3, SUM(system_size), AVG(commission3) FROM pv_jobs GROUP BY sales_person_3) 
UNION (SELECT sales_person_4, SUM(system_size), AVG(commission4) FROM pv_jobs GROUP BY sales_person_4) 
";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){    
echo $row['sales_person_1'];    
echo "<br />";    
echo $row['SUM(system_size)']. " Total score * Average Commission % of " . $row['AVG(commission1)'] ;    
echo "<br />";    
echo "Total " . $row['SUM(system_size)'] * $row['AVG(commission1)'] . " Personal score" ;    
echo "<br />";    
echo "<br />";    
}
?>

Thanks,
Jason