Sorting by expression in mySQL

Question

I have zones, and tickets sold within those zones....I use the following query to get the number of tickets sold per zone, and I need to sort so that the available zones (where thecount < capacity) are at the top of the sortorder:

SELECT z.name, z.capacity,count(t.ticketid) as thecount  
FROM tickets t JOIN zones z ON z.zoneid=t.zoneid 
GROUP BY z.name ORDER BY z.sortorder

returns:

+--------+----------+----------+
name       cap       thecount
+--------+----------+----------+
Zone1 |    100     |   100
Zone2 |     300    |   300
Zone3 |    250     |    101
Zone4 |    600     |    522
Zone5 |    160     |    160
+--------+----------+----------+

I need the sort to be in the following order:
Zone3
Zone4
Zone1
Zone2
Zone5

I'm not sure how to use an expression or something else to achieve this---any helpful hints?

Answer 1

So, based on GWilliams00 answer I ended up with :

SELECT *, capacity < thecount AS availableSeats
FROM (
   SELECT z.name, z.capacity,count(t.ticketid) as thecount  
   FROM gt_tickets t JOIN gt_zones z ON z.zoneid=t.zoneid  
   GROUP BY z.name

) t
ORDER BY availableSeats,sortorder

The 'capacity < thecount' evaluates to a boolean, so this can be used to sort the available zones , then sortorder keeps them in the right name order after that.

Answer 2

SELECT *, capacity - thecount AS availableSeats
FROM (
    SELECT z.name, z.capacity,count(t.ticketid) as thecount  
    FROM tickets t JOIN zones z ON z.zoneid=t.zoneid 
    GROUP BY z.name
) t
ORDER BY availableSeats, name

I think you might also need to group by z.name, z.capacity.

Answer 3

SELECT z.name, z.capacity,count(t.ticketid) as thecount   
FROM tickets t JOIN zones z ON z.zoneid=t.zoneid  
GROUP BY z.name  
order by case when thecount < c.capacity
              then thecount
              else c.capacity
         end