ORDER BY with CASE MySQL
<p>I have a "points standings" query, which shows statistics for a league I am in. I am attempting to order the query based off a few conditions.</p>

<ol>
<li>If the team is ranked 1-25 in points, order by wins, points</li>
<li>If the team is ranked outside the top 25 (26th to 42nd in points), order by points.</li>
</ol>

<p>The best way I could describe it, is that it should act like two tables. Teams ranked (1-25) should be sorted together, and teams ranked (26-42) should be sorted together.</p>

<p>Here is my query.</p>

<pre><code>SELECT m.Team AS team
, SUM( r.points ) AS points
, SUM(CASE
    WHEN rank = 1 THEN 1
    ELSE 0
  END) AS Wins
, SUM(CASE
    WHEN rank &lt; 6 THEN 1
    ELSE 0
  END) AS T5
, SUM(CASE
    WHEN rank &lt; 11 THEN 1
    ELSE 0
  END) AS T10
, SUM(CASE
    WHEN rank &lt; 21 THEN 1
    ELSE 0
  END) AS T20
, ROUND(Avg(r.points),2) AS ppr
, ROUND(Avg(r.rank),2) as avg_finish
, MIN(r.rank) as best
FROM members m
LEFT JOIN results r ON r.team_id = m.M_ID
GROUP BY team
ORDER BY 
    CASE
        WHEN COUNT(*) &lt; 26
        THEN Wins 
        WHEN COUNT(*) &gt; 25
        THEN points
    END DESC, points DESC;
</code></pre>

<p>I receive an error "Reference 'Wins' not supported (reference to group function) due to my alias "Wins". Instead, I've tried:</p>

<pre><code>ORDER BY 
    CASE
        WHEN COUNT(*) &lt; 26
        THEN SUM(CASE
            WHEN rank = 1 THEN 1
            ELSE 0
        END)  
        WHEN COUNT(*) &gt; 25
        THEN points
    END DESC, points DESC;
</code></pre>

<p>This successfully orders my table, but does not factor the COUNT(*) > 25 CASE clause, and just orders by wins, then points. Any ideas on how I could refactor the ORDER BY clause?</p>