Aggregate functions in ORDER BY

[Yasir Assam]

Hello,

I noticed something strange in MySQL (I'm using 4.1.15).
If I use an aggregate function in the ORDER BY clause I get an error.

SELECT men.man_name,
      COUNT(pets.pet_id)
FROM men,
    pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY COUNT(pets.pet_id) DESC;

gives me the error:

ERROR 1111 (HY000): Invalid use of group function

To get round this I can quote the ORDER BY column, e.g.

SELECT men.man_name,
      COUNT(pets.pet_id)
FROM men,
    pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `COUNT(pets.pet_id)` DESC;

This isn't an ideal solution though - in order for it to work I have to 
put that expression in the SELECT column list. If I want to use an 
aggregate function that isn't being displayed it just doesn't work 
because it can't find that column:

SELECT men.man_name,
      COUNT(pets.pet_id)
FROM men,
    pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `AVG(pets.pet_age)` DESC;

gives the result:

ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause'

I don't want to have to put the expression I want to order by in the 
SELECT column list - is there any way round this?

Below is the SQL code used to create the toy DB I'm using if anyone 
wants to experiment:

Thanks,
Yasir


DROP TABLE IF EXISTS men;
CREATE TABLE men (
  man_id INT UNSIGNED PRIMARY KEY,
  man_name VARCHAR(255)
);

INSERT INTO men (man_id, man_name) VALUES
  (1, 'Alan'),
  (2, 'Bob'),
  (3, 'Colin');

DROP TABLE IF EXISTS pets;
CREATE TABLE pets (
  pet_id INT UNSIGNED PRIMARY KEY,
  pet_name VARCHAR(255),
  pet_man_id INT UNSIGNED,
  pet_age INT
);

INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES
  (1, 'Rex', 1, 5),
  (2, 'Buddy', 1, 10),
  (3, 'Sam', 1, 13),
  (4, 'Lucky', 1, 3),
  (5, 'Tiny', 1, 7),
  (6, 'Snoopy', 2, 4),
  (7, 'Lady', 2, 9),
  (8, 'Tiger', 2, 6),
  (9, 'Speedy', 3, 1);