SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •