Jump to content

MYSQL: group by & order by


Borderline
 Share

Recommended Posts

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 by Borderline
Link to comment
Share on other sites

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

Edited by Err
Link to comment
Share on other sites

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:comptipsterselections.jpg Comptipsterboards:comptipsterboard.jpg 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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...