出现就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
--转自