GROUP BY and ORDER BY together

[biohazardep]

I'm trying to run the next query:

SELECT username, COUNT(item_id) 
FROM (phpbb_lms_items LEFT JOIN phpbb_lms_mochilas ON item_moch=moch_id)
LEFT JOIN phpbb_users ON moch_dueno=user_id
GROUP BY moch_dueno
ORDER BY COUNT(item_id) DESC

 

It works as i want it to in my windows server, but not in my linux server. In the linux box i get the message:

"SQL Error : 1111 Invalid use of group function"

 

The windows server has the following versions:

Apache Web Server Version 2.2.3

PHP Version 5.1.6

MySQL Database Version 5.0.24a

 

The linux one has:

Apache Web Server Version 1.3.33

PHP Version 4.3.11

MySQL Database Version 5.0.18

 

Can anyone help me get it to work in the linux server please?

 

Note: The linux box is from paid hosting, so i can't do much about what versions or features it has. My windows server is only for testing and development before i move my code to the live server (the linux one).

[bubblegum.anarchy]

This has nothing to do with the error, but what does the paranthesis wrapping the first join mean?

 

Might be missing the .user_id on the GROUP BY

 

GROUP BY moch_dueno.user_id

[fenway]

It's compliaining because you've included non-grouped by columns in your select list.

[biohazardep]

But it works when i take out the ORDER BY part, like this:

 

SELECT username, COUNT(item_id) 
FROM (phpbb_lms_items LEFT JOIN phpbb_lms_mochilas ON item_moch=moch_id)
LEFT JOIN phpbb_users ON moch_dueno=user_id
GROUP BY moch_dueno

[fenway]

That is very interesting... but how can that output have any meaning unless moch_dueno and username are 1-1?