MYSQL: group by & order by

[Borderline]

Good evening I'm attempting to produce some tipping results, with the biggest profit at the top of the table, and working down to the lowest score (that'll be me, then!) There are several tips for each contestant, so I've used the group by command - this works fine, and produces the result I'd expect. However, when I try the order by part, the results remain unordered. I hoped someone could point out what I expect is as schoolgirl error. The code is as follows:

	<?php  	// Make a MySQL Connection	 $query = "SELECT stable, SUM(profit) FROM comptipsterselections		 WHERE date = '12/04/12'		 GROUP BY stable ORDER BY profit";	  	 $result = mysql_query($query) or die(mysql_error());	// Set-up table  	 echo "<table class='correctenglish' border='1' cellpadding='4' cellspacing='0' width='95%'>";	 echo "<tr class='toprow'>  <th>Stable</th> <th>Daily Profit</th></tr>";    	// Print out result	 while($row = mysql_fetch_array($result)){		 echo  "<tr><td>";	 echo  $row['stable'];	 echo  "</td><td>";	 echo " £". $row['SUM(profit)'];	 echo  "</td></tr>";	 }	 echo  "</table>";	?>

Any assistance would be appreciated. The current results can be found here: http://www.further-flight.co.uk/site/competitions/tipster/aintr/daily/20120412.php Many thanks,

Edited April 17, 2012 by Borderline

[Err]

Try: [...] ORDER BY profit DESCOr: [...] ORDER BY SUM(profit) DESCORDER BY does ascending (ASC) by default.

Edited April 12, 2012 by Err

[Borderline]

Or: [...] ORDER BY SUM(profit) DESC

Works perfectly, thanks so much for your help.

[Borderline]

Hoping you can save me from my own incompetence again. I'd trying to join info from two tables - but I'm not sure I can produce what I want from the info I have. I'd like to produce a table that shows the Stable Name and Profit from comptipsterselections (as per the example above), where the stable name is an url produced from table comptipsterboard. If this works, I can reduce the amount of info in the comptipsterboard table. Comptipsterselections: Comptipsterboards: Please could you advise where I've gone wrong in the code below:

    <?php       // Make a MySQL Connection	 $query = "SELECT comptipsterselections.stable, comptipsterselections.SUM(profit), comptipsterboard.link		 FROM comptipsterselections		 LEFT JOIN comptipsterboard		 ON comptipsterselections.stable=comptipsterboard.stable		 WHERE comp = 'aintree 2010'		 GROUP BY comptipsterselections.stable ORDER BY SUM(comptipsterselections.profit) DESC";			   	  	 $result = mysql_query($query) or die(mysql_error());    // Set-up table   	 echo "<table class='correctenglish' border='1' cellpadding='4' cellspacing='0' width='75%'>";	 echo "<tr class='toprow'>  <th>Stable</th> <th>Daily Profit</th></tr>";          // Print out result	 while($row = mysql_fetch_array($result)){		 echo  "<tr><td>";	 echo  "<a href='$link'>";	 echo  $row['stable'];	 echo  "</td><td>";	 echo  " £". $row['SUM(profit)'];	 echo  "</td></tr>";	 }	 echo  "</table>";    ?>

Any advice greatly appreciated.

[CCZ]

I guess you get an error on comp being ambigious since both tables have a field named comp. If so, you need to change this line: WHERE comptipsterboard.comp = 'aintree 2010'

Edited April 20, 2012 by CCZ