Handling multiple GROUP BY statements in a query

[bostboy]

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

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

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

[r937]

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

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

[bostboy]

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.