MySQL ORDER BY Aggregate column returning incorrect results

Question

Im struggling to get this query to be able to order by stakes correctly - it returns values that are neither desc or asc. It does work with wins if I use order by. Perhaps MySQL did not evaluate the sort order at the time it was building the aggregation?

I've stripped out some of the full names to make it less tedious to read.

SELECT a.b AS  t , COUNT(  c.aID ) AS  r , 
                   COUNT( 
                          CASE WHEN  c.Finish =1
                               THEN 1 
                               ELSE NULL 
                               END )               AS  wins , 
                 CONCAT( FORMAT( (
                                   COUNT( 
                                          CASE WHEN  c.Finish =1
                                               THEN 1 
                                               ELSE NULL 
                                               END ) / COUNT(  c.aID ) ) *100, 0 ) ,  '%'
                       ) AS  Percent , 
                FORMAT( SUM(  c.StakeWon ) , 0 ) AS  stakes 
 FROM c
 INNER JOIN a ON c.aID = a.aID
 INNER JOIN d ON c.dID = d.dID
 WHERE  d.w >= STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) 
 AND  d.w < STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) + INTERVAL 1 
 MONTH 
 GROUP BY  a.b 
 ORDER BY  stakes DESC`

It also doesnt work if I order by Percent. I didnt want to ask this question here but this is driving me crazy.

Answer 1

It would help if you show some of the result, but you must sort the stakes before you format it. Then you really sort the numeric value rather than the formatted string.

Here is an example SQL Fiddle how it would go wrong.

The numeric values are not sorted descending because of the formatting

100
10,000,000
-5,000,005

So you would do something like

...
     FORMAT( SUM(  c.StakeWon ) , 0 ) AS  stakes ,
     SUM(  c.StakeWon )  AS  stakes_num 
...
     ORDER BY stakes_num desc

Example how it would work: SQL Fiddle

Answer 2

Try using this whole expression

COUNT(  c.aID ) AS  r , 
                   COUNT( 
                          CASE WHEN  c.Finish =1
                               THEN 1 
                               ELSE NULL 
                               END )               AS  wins , 
                 CONCAT( FORMAT( (
                                   COUNT( 
                                          CASE WHEN  c.Finish =1
                                               THEN 1 
                                               ELSE NULL 
                                               END ) / COUNT(  c.aID ) ) *100, 0 ) ,  '%'
                       ) AS  Percent , 
                FORMAT( SUM(  c.StakeWon ) , 0 )

in the order by clause instead of alias. My observation is, Alias doesn't work this way

Probably like this

SELECT a.b AS  t , COUNT(  c.aID ) AS  r , 
                   COUNT( 
                          CASE WHEN  c.Finish =1
                               THEN 1 
                               ELSE NULL 
                               END )               AS  wins , 
                 CONCAT( FORMAT( (
                                   COUNT( 
                                          CASE WHEN  c.Finish =1
                                               THEN 1 
                                               ELSE NULL 
                                               END ) / COUNT(  c.aID ) ) *100, 0 ) ,  '%'
                       ) AS  Percent , 
                FORMAT( SUM(  c.StakeWon ) , 0 ) AS  stakes 
 FROM c
 INNER JOIN a ON c.aID = a.aID
 INNER JOIN d ON c.dID = d.dID
 WHERE  d.w >= STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) 
 AND  d.w < STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) + INTERVAL 1 
 MONTH 
 GROUP BY  a.b 
 ORDER BY  COUNT(  c.aID ) AS  r , 
                   COUNT( 
                          CASE WHEN  c.Finish =1
                               THEN 1 
                               ELSE NULL 
                               END )               AS  wins , 
                 CONCAT( FORMAT( (
                                   COUNT( 
                                          CASE WHEN  c.Finish =1
                                               THEN 1 
                                               ELSE NULL 
                                               END ) / COUNT(  c.aID ) ) *100, 0 ) ,  '%'
                       ) AS  Percent , 
                FORMAT( SUM(  c.StakeWon ) , 0 ) DESC`