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);
