Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

MySQL: Count comments (Distinct) on many to many pair userid/topicid

1 view
Skip to first unread message

aNickname

unread,
Mar 21, 2007, 4:28:26 PM3/21/07
to
I have a mySql table like this

userid (int)
topicid (int)
text (varchar)

It stores comments for a small forum.
Table entries may be like this, to give an idea

20 18 'hi'
20 18 'hello to you'
20 18 'how are you?'
20 18 'fine'
20 654 'i dont like it'
20 654 'i like it'
310 87 'i prefer green'
310 87 'i prefer red'
310 18 'i dont know it' <-- note this: different userif but the topic
id can be prsent for userid 20 too

So, it is basically the "tuple" userid topicid that identifies a
member space.

Now, my question is: regardless of which user (that is, I want to know
for all users) and regardless of specific topicid (that is, I want to
know for all topicids) how can I return: (userid, topicid, COUNT of
comments for that "tuple") WITHOUT having duplicates and being sure
the count of comments per each topic/user pair is correct?

I tried in several fashions with distinct, count, group by but i
always ended either with duplicates or with wrong counts.
Please note I don't need only the count but the 3 data: userid,
topicid count(how many comments for that couple, no duplicates)

Any idea? I'm not an expert and neither a beginner, but this quesry is
really giving troubles to me and I can't trust the fact in one
occasion seemed right: I need be sure it's right, I can't code by
chance :-)

thanks

strawberry

unread,
Mar 21, 2007, 6:24:36 PM3/21/07
to

I don't quite understand. What result would you expect from the data
snippet provided?

Is this right?

20 18 4

20 654 2

310 87 2

310 18 1

Or some other result?

aNickname

unread,
Mar 22, 2007, 1:34:06 PM3/22/07
to
I found it.
I share the solution in case someone else may benefit from it

GROUP BY userid, topicid

In that way, it extracts only the _combinations_ of user and topic
that are unique. Count is then applied on those combinations, without
duplicates. Having used GROUP BY thus far only on one column, I didn't
realize actually that grouping by more fields, it does not group
anymore by COLUMN but by ROW, which was what the case required.

Thank you for your help anyway!

0 new messages