Can you run without the Order By at all? If not, you may need to properly join the tables.
I could reproduce the issue with MySQL 4.1 as well and I would go around it myself by creating a temporaty table, populating it with the aggregate, doing a select on the temp table and then of course dropping it. -----Original Message----- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 7:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY 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); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]