[Resolved] Grouping, Counting and Playing with Varchars

[bubblenut]

Hi I believe it will be easiest to explain my problem with the aid of a couple of examples. The following query works fine and returns the expected result with no errors.

SELECT a.uid as uid, COUNT(b.uid) as b_count FROM a LEFT JOIN b ON a.uid=b.fid GROUP BY a.uid ORDER BY `count(b.uid)`;

However the following does not work at all, it throws a ERROR 1054: Unknown column 'count(b.uid)' in 'b'
other_var is a varchar value which holds numbers (I know it's terrible but it can't really be changed as the table has almost 20 million rows in and we can't afford to let the server go down for too long)

SELECT a.uid as uid, COUNT(b.uid) as b_count FROM a LEFT JOIN b ON a.uid=b.fid GROUP BY a.uid ORDER BY (LEFT(a.other_var,2)+`count(b.uid)`);

Answers on a postcard
Cheers
Bubble

[bubblenut]

Figured it out! I put all the funky stuff in the select and then used the name I'd linked to it in the order by like so.

SELECT a.uid as uid, COUNT(b.uid) as count_b, (LEFT(a.other_var,2)+COUNT(b.uid)) as counter FROM a LEFT JOIN b ON (a.uid=b.fid) GROUP BY a.uid ORDER BY counter

Hope this can help someone else as well
Bubble