Page 1 of 1

MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 12:45 pm
by lauthiamkok
Hi,

I have 3 tables,

- pages (with page id, etc)
- tags (tag id, and tag name)
- tagged (tagged id, tag id, and page id)

this is my SQL query,

Code: Select all

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
FROM root_tags
LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
WHERE root_tagged.pg_id != '0'
GROUP BY root_tags.tag_id
ORDER BY COUNT(root_tagged.pg_id) DESC
LIMIT 0,20
in my localhost, it returns the result I want, such as,

Code: Select all

tag_id  tag_name    COUNT( root_tagged.pg_id )
28  illustration    20
1   typography  15
33  floral  11
3   decorative  11
7   vector  11
36  drawing     9
6   design  9
8   texture     9
16  experimental    9
29  branding    7
31  ornate  7
34  pattern     5
4   editorial   5
37  textured    4
19  packaging   3
35  crest   3
5   advertising     3
30  photo-montage   2
38  symmetry    2
13  line-drawing    2
I want to order the result in the way that higher numbers always comes first on top.

But in my live server, it returns this error message,

MySQL said: Documentation
#1111 - Invalid use of group function


It only works fine when this line below is removed but the result cannot be order where higher numbers always comes first...

Code: Select all

ORDER BY COUNT(root_tagged.pg_id) DESC
any ideas??

many thanks if you can help please...

cheers,
Lau

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 2:07 pm
by Darhazer
Try:

Code: Select all

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id ) AS total
FROM root_tags
LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
WHERE root_tagged.pg_id != '0'
GROUP BY root_tags.tag_id
ORDER BY total DESC
LIMIT 0,20
If doesn't work, please tell us the version of MySQL on your life server.

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 2:11 pm
by requinix
Pretty sure you need MySQL 5.0+ to do an ORDER BY with an aggregate function.

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 5:32 pm
by lauthiamkok
Darhazer wrote:Try:

Code: Select all

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id ) AS total
FROM root_tags
LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
WHERE root_tagged.pg_id != '0'
GROUP BY root_tags.tag_id
ORDER BY total DESC
LIMIT 0,20
If doesn't work, please tell us the version of MySQL on your life server.
yes, this one is working thanks!

I also worked it out in this way,

Code: Select all

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
FROM root_tags
LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
WHERE root_tagged.pg_id != '0'
GROUP BY root_tags.tag_id
ORDER BY 3 DESC
LIMIT 0,20
but I don't know why it is number 3 but why not 1 or 2...?

thanks :)

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 5:34 pm
by lauthiamkok
tasairis wrote:Pretty sure you need MySQL 5.0+ to do an ORDER BY with an aggregate function.
I'm not sure what version of MySQL is with the server, but I can tell it is an older version as lots of other queries went wrong as well and I had to work them around...

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 5:36 pm
by Eran
but I don't know why it is number 3 but why not 1 or 2...?
It's the position of the column you want to sort by (the third column you are selecting). An alternative syntax to specifying the column name directly

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 5:49 pm
by lauthiamkok
Darhazer wrote: If doesn't work, please tell us the version of MySQL on your life server.
just found out what version of MySQL the serving is running on,

Server version: 4.1.25-Debian_mt1

arggggg....

Re: MYSQL grouping result: Invalid use of group function

Posted: Sun Mar 21, 2010 7:05 pm
by lauthiamkok
pytrin wrote:
but I don't know why it is number 3 but why not 1 or 2...?
It's the position of the column you want to sort by (the third column you are selecting). An alternative syntax to specifying the column name directly
oic, got it! thank you :D