SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Handling multiple GROUP BY statements in a query

    I have a need to get record counts from a sub-table and concatenated values from another group of sub-tables. The concatenation problem was handled in a different thread. The problem now is separating the counts from the concatenations. What is happening is I am getting the counts multiplied by the number of concatenated values returned by the group by statement.

    The table structure is as follows
    Code:
    forums as t1 - forumid, userid
    forumsubcats as t2 - forumid, subcatid
    subcats as t3 - subcatid, categoryid, subcatname
    categories as cat - categoryid, catname
    
    then the other table that I need to get the count from is called 
    posts as p - postid, forumid
    
    and then there is a single join to get a username from a userid
    users as u - userid, username
    The query currently looks like this
    Code:
    SELECT 	t1.forumid,
    		GROUP_CONCAT(distinct cat.catname, '-', subcatname) AS categories, 
    		COUNT( p.forumid ) AS counter,
    		username
    	FROM forums as t1 
    INNER JOIN forumsubcats as t2 
        ON t2.forumid = t1.forumid
    INNER JOIN subcats as t3 
        ON t2.subcatid = t3.subcatid
    INNER JOIN categories AS cat
        ON cat.categoryid = t3.categoryid
    INNER JOIN posts as p 
        ON p.forumid = t1.forumid 
    INNER JOIN users as u 
        ON u.userid = t1.userid 
    GROUP BY t1.forumid
    The concatenations work great, but if there are 3 values concatenated, the count is multiplied by 3. Also, if I strip the concatenations out of the qurey, the counts work fine. Any help as to how to separate the concatenations from the count would be greatly appreciated. Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    when debugging a query like this, it is often most helpful to see the actual data that you are trying to perform aggregations on

    please run this query, and display the results --
    Code:
    SELECT t1.forumid
         , u.username
         , t3.subcatname
         , cat.catname
         , p.postid
      FROM forums as t1 
    INNER 
      JOIN forumsubcats as t2 
        ON t2.forumid = t1.forumid
    INNER 
      JOIN subcats as t3 
        ON t2.subcatid = t3.subcatid
    INNER 
      JOIN categories AS cat
        ON cat.categoryid = t3.categoryid
    INNER 
      JOIN posts as p 
        ON p.forumid = t1.forumid 
    INNER 
      JOIN users as u 
        ON u.userid = t1.userid 
    ORDER 
        BY t1.forumid
         , u.username
         , t3.subcatname
         , cat.catname
         , p.postid
    i expect that this should illustrate the problem you are facing prior to applying a GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That helps to see what is going on. After looking at that, if I change the counter line to

    "COUNT(distinct p.postid ) AS counter,"

    it works fine. Thanks for the tip. I had actually tried to do that after reading that in the book but couldn't get the select right. So that is a great pointer also. Anyhow, problem solved. I really appreciate the input.


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
  •