Need help refining a mySQL query

Discussion in 'MySQL' started by eddiemoth, Jun 2, 2009.

0
  1. #1
    Folks,
    I am trying to write a query to find users who has posted more than 30 posts withing a month, i.e., in May 2009. With some help from other folks I got it written as below but it does not seem to produce the result I am looking for. It seems like it brings any user who posted any post in May 2009 - not just greater than 30 post. Does anybody have any hint or suggestions?

    Thanks

      

SELECT COUNT( postid ) AS count, bbuser.username AS username
FROM bbpost
LEFT JOIN bbuser ON (
bbuser.userid = bbpost.userid
)
WHERE dateline > unix_timestamp( '2009-01-02' )
GROUP BY username
HAVING COUNT( postid ) >30
ORDER BY count DESC
LIMIT 0 , 30
    Code (markup):

     
    eddiemoth, Jun 2, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    The JOIN could be incorrect. Also, you should name the tables wherever you are naming a column. COUNT(,

    See if this method works better.

    SELECT COUNT( bbpost.postid ) AS total, bbuser.username AS username
    FROM bbpost
    LEFT JOIN bbuser ON bbpost.userid = bbuser.userid
    WHERE bbpost.dateline > unix_timestamp( '2009-01-02' )
    GROUP BY username
    HAVING total >30
    ORDER BY total DESC
    LIMIT 0 , 30;

    Another thing that seems like it could be troublesome, is the dateline field. What is the data type of this. MySQL does not store data in a UNIX_TIMESTAMP format which you would see in php or another linux programming language as TIME, so converting to this may result in, no result.
     
    jestep, Jun 3, 2009 IP
  3. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Why are you using the join, I might by sleepy, but I can't see it and loose the limit because you might miss information that way.
    I changed the datetime comparison into mysql year/month syntax, the calls for your database server might vary e.g. PostgreSQL uses an extract function.

    SELECT COUNT( postid ) AS count, username AS username
    FROM bbpost
    WHERE year(dateline)=2009 and month(dateline)=02
    GROUP BY username
    HAVING COUNT( postid ) >30
    ORDER BY count DESC
     
    chisara, Jun 8, 2009 IP