Professional Web Applications Themes

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

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

  1. #1

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

    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

    aNickname Guest

  2. #2

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

    aNickname wrote: 

    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?

    strawberry Guest

  3. #3

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

    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!

    aNickname Guest

Similar Threads

  1. distinct, count
    By Frances in forum MySQL
    Replies: 3
    Last Post: December 2nd, 07:42 PM
  2. Needed, COUNT(DISTINCT *) workaround for ACCESS
    By rickaclark54 in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 14th, 08:07 PM
  3. count word repetitions for each pair of lines
    By tony in forum PERL Miscellaneous
    Replies: 1
    Last Post: July 23rd, 02:22 PM
  4. count of distinct rows
    By priya in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 19th, 08:27 AM
  5. Count two distinct items in a table.
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 16th, 04:57 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139