MYSQL grouping result: Invalid use of group function

出现就google，以下文章解决了问题

 

http://www.sitepoint.com/forums/showthread.php?666125-MYSQL-grouping-result-Invalid-use-of-group-function

MYSQL grouping result: Invalid use of group function

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 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:

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:

ORDER BY COUNT(root_tagged.pg_id) DESC

any ideas??

many thanks if you can help please...

cheers,
Lau

<!--

-->

<!--

-->

---

---

Join Date

Jul 2002

Location

Toronto, Canada

Posts

36,347

Code:

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

 

---

Code:

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

thank you. it works perfectly.

i also worked it out in this way,

Code:

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,

Code:

ORDER BY 1 DESC

thanks!