Single MySQL query with GROUP option

Discussion in 'MySQL' started by Scavenger, Feb 22, 2009.

0
  1. #1
    I use the following queries:

    1. to extract date & number of activated users
    SELECT DATE(i_date),count(*) FROM users where user_status=2 group by DATE(i_date)

    o/P: 2009/02/22, 393

    2. Date & number of all registered users
    SELECT DATE(i_date),count(*) FROM users group by DATE(i_date)

    o/P: 2009/02/22, 585

    3. Date & messages sent on that date
    SELECT DATE(sentdate),count(*) FROM messages group by DATE(sentdate)

    o/P: 2009/02/22, 36


    I want a single query, whole result-set has - Date, count of active users, count of all registrations, count of messages sent.

    o/P: 2009/02/22, 393, 585, 36
     
    Scavenger, Feb 22, 2009 IP
  2. bl4ckwolf

    bl4ckwolf Active Member

    Messages:
    216
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #2
    I suggest you give a try to subselect.

    SELECT DATE(i_date),count(*), (SELECT DATE(i_date),count(*) FROM users group by DATE(i_date)) as userCount, (SELECT DATE(sentdate),count(*) FROM messages group by DATE(sentdate)) as todayMessage FROM users where user_status=2 group by DATE(i_date)

    Example from : http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
     
    bl4ckwolf, Feb 22, 2009 IP