MYSQL grouping result: Invalid use of group function

Databases
1

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,

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,

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…

ORDER BY COUNT(root_tagged.pg_id) DESC

any ideas??

many thanks if you can help please…

cheers,
Lau

2 
SELECT root_tags.tag_id
     , root_tags.tag_name
     , COUNT( root_tagged.pg_id ) AS howmany
  FROM root_tags
LEFT OUTER
  JOIN root_tagged 
    ON root_tagged.tag_id = root_tags.tag_id 
   AND root_tagged.pg_id <> 0
GROUP 
    BY root_tags.tag_id
ORDER 
    BY howmany DESC LIMIT 0,20
3

thank you. it works perfectly.

i also worked it out in this way,

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 1 DESC
LIMIT 0,20

just putting a number in this line,

ORDER BY 1 DESC

thanks!:slight_smile: