SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Of subqueries and variables

    I'm trying to get a list of partners (roleid = 511) who posted comments in the month of April of this year.
    I would like to embed the count(*) of comments in each row.
    And I would like to sort the results by the partner having the most comments first and decend to the partner with the least comments (but > 0) at the bottom of the list.

    Here's what I've come up with (which is not working):
    Code MySQL:
    SELECT u.uid, u.name, u.mail (SELECT @comment_count:=count(*) FROM comments c WHERE c.uid = u.uid AND EXTRACT(YEAR_MONTH FROM c.timestamp) = '201204') AS april comments, DATE_FORMAT(FROM_UNIXTIME(u.access),'%b-%d-%Y') AS 'last visit'
      FROM users AS u
        INNER
          JOIN users_roles AS ur
            ON ur.uid = u.uid
          AND ur.rid = 511
      WHERE u.mail NOT LIKE '%example.com%' AND EXTRACT(YEAR_MONTH FROM u.access) > '201204' ORDER BY @comment_count;
     
     
    mysql> describe users;
    +------------------+------------------+------+-----+---------+----------------+
    | Field            | Type             | Null | Key | Default | Extra          |
    +------------------+------------------+------+-----+---------+----------------+
    | uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name             | varchar(60)      | NO   | UNI |         |                |
    | pass             | varchar(32)      | NO   |     |         |                |
    | mail             | varchar(64)      | YES  | MUL |         |                |
    | mode             | tinyint(1)       | NO   |     | 0       |                |
    | sort             | tinyint(1)       | YES  |     | 0       |                |
    | threshold        | tinyint(1)       | YES  |     | 0       |                |
    | theme            | varchar(255)     | NO   |     |         |                |
    | signature        | varchar(255)     | NO   |     |         |                |
    | created          | int(11)          | NO   | MUL | 0       |                |
    | access           | int(11)          | NO   | MUL | 0       |                |
    | status           | tinyint(4)       | NO   | MUL | 0       |                |
    | timezone         | varchar(8)       | YES  |     | NULL    |                |
    | language         | varchar(12)      | NO   |     |         |                |
    | picture          | varchar(255)     | NO   |     |         |                |
    | init             | varchar(64)      | YES  |     |         |                |
    | data             | longtext         | YES  |     | NULL    |                |
    | login            | int(11)          | NO   |     | 0       |                |
    | timezone_name    | varchar(50)      | NO   |     |         |                |
    | signature_format | smallint(6)      | NO   |     | 0       |                |
    +------------------+------------------+------+-----+---------+----------------+
    20 rows in set (0.00 sec)
     
    mysql> describe comments;
    +-----------+---------------------+------+-----+---------+----------------+
    | Field     | Type                | Null | Key | Default | Extra          |
    +-----------+---------------------+------+-----+---------+----------------+
    | cid       | int(10)             | NO   | PRI | NULL    | auto_increment |
    | pid       | int(10)             | NO   | MUL | 0       |                |
    | nid       | int(10)             | NO   | MUL | 0       |                |
    | uid       | int(10)             | NO   | MUL | 0       |                |
    | subject   | varchar(64)         | NO   |     |         |                |
    | comment   | longtext            | NO   |     | NULL    |                |
    | hostname  | varchar(128)        | NO   |     |         |                |
    | timestamp | int(11)             | NO   |     | 0       |                |
    | status    | tinyint(3) unsigned | NO   | MUL | 0       |                |
    | format    | smallint(6)         | NO   |     | 0       |                |
    | thread    | varchar(255)        | NO   |     |         |                |
    | name      | varchar(60)         | YES  |     | NULL    |                |
    | mail      | varchar(64)         | YES  |     | NULL    |                |
    | homepage  | varchar(255)        | YES  |     | NULL    |                |
    +-----------+---------------------+------+-----+---------+----------------+
    14 rows in set (0.00 sec)
     
    mysql> describe role;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | rid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(64)      | NO   | UNI |         |                |
    +-------+------------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    caution: untested
    Code:
    SELECT u.uid
         , u.name
         , u.mail 
         , FROM_UNIXTIME(u.access,'%b-%d-%Y') AS 'last visit'
         , c.april_comments
      FROM users AS u
    INNER
      JOIN users_roles AS ur
        ON ur.uid = u.uid
       AND ur.rid = 511
    INNER
      JOIN ( SELECT uid
                  , COUNT(*) AS april_comments
               FROM comments 
              WHERE timestamp >= UNIX_TIMESTAMP('2012-04-01')
                AND timestamp  < UNIX_TIMESTAMP('2012-05-01')
             GROUP
                 BY uid ) AS c  
        ON c.uid = u.uid 
     WHERE u.mail NOT LIKE '%example.com%' 
       AND u.access >= UNIX_TIMESTAMP('2012-05-01') 
    ORDER 
        BY c.april_comments DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937.
    Works like a charm!


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
  •