SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to figure out how to count something. Here's an idea of what the table looks like (it's the vBulletin thread table):

    Code:
    postusername		dateline
    James			9999
    John			9998
    Kevin			9997
    Joey			9991
    John			9971
    Kevin			9850
    John			9831
    Kevin			9798
    Joey			9791
    John			9752
    Joey			9751
    Rick			9700
    Now what I'm trying to do is count the number of times each name appears within certain parameters for the dateline. e.g. WHERE (dateline>9830 or dateline<9998), and return the name that appears the most times, along with how many times it appears. Is there any way to do this? I have tried everything I can think of (which I admit isn't much ) but to no avail.

    Thanks
    Last edited by tubedogg; May 8, 2001 at 23:33.
    Kevin

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firstly, I assume you want to use an AND in that WHERE CLAUSE instead of OR - that is you want to limit the results to the range 9830 < X < 9998 ?

    How about:

    SELECT postusername, COUNT(*) AS postcount
    FROM TableName
    WHERE dateline > 9830
    AND dateline < 9998
    GROUP BY postusername
    ORDER BY postcount DESC
    LIMIT 1

  3. #3
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you're right about the AND. And that query worked. Thanks so much!
    Kevin

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    np. BTW, I didn't find anything stupid with that question???

  5. #5
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stupid only as in "I'm sure I'm missing something obvious here". Which was the case - when I read your reply, I had figured out the date thing, and basically had the same query you gave, but it was missing the group by.

    Kevin


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
  •