We help IT Professionals succeed at work.

order by column that is result of sum()

I have a query which joins results from two tables one regarding members informaiton and another with monetary values attributed to them.

One of the columns uses a result from sum(), summing a dollar amount. I want to order the results of the query by that particular column DESC so that the larger summed amount is at the top of the list.

Here is my current query:

$selrecord_yma = "2010";
if (isset($_POST['selrecord'])) {
  $selrecord_yma = (get_magic_quotes_gpc()) ? $_POST['selrecord'] : addslashes($_POST['selrecord']);
}
mysql_select_db($database_bninsteins_mysql, $bninsteins_mysql);
$query_yma = sprintf("SELECT s.member_id, s.year, SUM(s.amount) AS 'memberTotal', b.status, b.first_name, b.last_name FROM SMTM AS s JOIN members AS b ON s.member_id = b.member_id WHERE s.year = %s GROUP BY b.status, b.last_name, b.first_name ORDER BY 'memberTotal' DESC", GetSQLValueString($selrecord_yma, "int"));
$yma = mysql_query($query_yma, $bninsteins_mysql) or die(mysql_error());
$row_yma = mysql_fetch_assoc($yma);
$totalRows_yma = mysql_num_rows($yma);


I have also tried ordering by s.amount with no success. It comes up with and order, but not numerical.

The sum() works properly, but the order by doesn't result in a greater to lesser amounts list of rows.

Thanks for your patience as I am not sure I am even asking this question correctly. I appreaciate your help.
Comment
Watch Question

Reza RadConsultant, Trainer
Commented:
try this one:

$selrecord_yma = "2010";if (isset($_POST['selrecord'])) {  $selrecord_yma = (get_magic_quotes_gpc()) ? $_POST['selrecord'] : addslashes($_POST['selrecord']);}mysql_select_db($database_bninsteins_mysql, $bninsteins_mysql);$query_yma = sprintf("SELECT s.member_id, s.year, SUM(s.amount) AS 'memberTotal', b.status, b.first_name, b.last_name FROM SMTM AS s JOIN members AS b ON s.member_id = b.member_id WHERE s.year = %s GROUP BY b.status, b.last_name, b.first_name ORDER BY 3 DESC", GetSQLValueString($selrecord_yma, "int"));$yma = mysql_query($query_yma, $bninsteins_mysql) or die(mysql_error());$row_yma = mysql_fetch_assoc($yma);$totalRows_yma = mysql_num_rows($yma);


Reza RadConsultant, Trainer

Commented:
you can use column number of your result in Order by clause, but you can not use alias names in order by.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Yup, either use the column number, subquery it to use the alias or repeat the formula.

e.g.

SELECT s.member_id, s.year, SUM(s.amount) AS `memberTotal`, b.status, b.first_name, b.last_name FROM SMTM AS s JOIN members AS b ON s.member_id = b.member_id WHERE s.year = %s GROUP BY b.status, b.last_name, b.first_name ORDER BY SUM(s.amount) DESC

Using the column number can be unpredictable if you suddenly edit the query and added a new column somewhere without noticing the order by column position clause.

SELECT * FROM (
SELECT s.member_id, s.year, SUM(s.amount) AS `memberTotal`, b.status, b.first_name, b.last_name FROM SMTM AS s JOIN members AS b ON s.member_id = b.member_id WHERE s.year = %s GROUP BY b.status, b.last_name, b.first_name
) SQ ORDER BY memberTotal DESC

And a quick note on why your query ran (no syntax error) but did not seem to order.
ORDER BY 'memberTotal' DESC

Mysql single quotes are for text, backquotes are for column names. In effect, you are asking for ordering on the constant text 'memberTotal', which might as well have been written
ORDER BY 'someconstanttext' DESC
because it has no relationship to the column aliases as `memberTotal`