MYSQL grouping result: Invalid use of group function

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lauthiamkok
Forum Contributor
Posts: 153
Joined: Wed Apr 01, 2009 2:23 pm
Location: Plymouth, United Kingdom

MYSQL grouping result: Invalid use of group function

Post 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
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: MYSQL grouping result: Invalid use of group function

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MYSQL grouping result: Invalid use of group function

Post by requinix »

Pretty sure you need MySQL 5.0+ to do an ORDER BY with an aggregate function.
lauthiamkok
Forum Contributor
Posts: 153
Joined: Wed Apr 01, 2009 2:23 pm
Location: Plymouth, United Kingdom

Re: MYSQL grouping result: Invalid use of group function

Post 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 :)
lauthiamkok
Forum Contributor
Posts: 153
Joined: Wed Apr 01, 2009 2:23 pm
Location: Plymouth, United Kingdom

Re: MYSQL grouping result: Invalid use of group function

Post 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...
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MYSQL grouping result: Invalid use of group function

Post 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
lauthiamkok
Forum Contributor
Posts: 153
Joined: Wed Apr 01, 2009 2:23 pm
Location: Plymouth, United Kingdom

Re: MYSQL grouping result: Invalid use of group function

Post 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....
lauthiamkok
Forum Contributor
Posts: 153
Joined: Wed Apr 01, 2009 2:23 pm
Location: Plymouth, United Kingdom

Re: MYSQL grouping result: Invalid use of group function

Post 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
Post Reply