Mysql count id query

Question

I'm using mysql table like this one:

|  userid  |  regdate   | question  | answer  |
-----------------------------------------------
      1      2010-10-14   question 1  answer1
      2      2010-10-14   question 2  answer2    
      3      2010-10-15   question 3  answer3
      4      2010-10-16   question 4  answer4

I want to count registered users per day and the number of questions and answers sent per day. Can I do it with one mysql query and how?

The result should be something like:

|  regdate  |  new users  |  questions sent  | answers sent  |
--------------------------------------------------------------
 2010-10-14        2               2                 2
 2010-10-15        1               1                 1
 2010-10-16        1               1                 1

Answer 1

maybe:

SELECT regadte, 
       COUNT(userid) as new_users, 
       COUNT(question) as questions_sent, 
       COUNT(answer) as answers_sent 
FROM table 
GROUP BY regdate;

I am not sure though because, do you mean "newly registered users" or "logged in users" ? Also, from your table, it looks like a user has to send an answer for each question, which I doubt is the case...

Answer 2

Try this

select regdate,count(userid) as new_users,count(question) as question_sent,count(answer) as answers_sent
from table_name group by regdate

Answer 3

If you only want to see counts where there's at least one question or answer for a date, use:

  SELECT t.regdate, 
         COUNT(t.userid) as new_users, 
         COUNT(t.question) as questions_sent, 
         COUNT(t.answer) as answers_sent 
    FROM YOUR_TABLE t
GROUP BY t.regdate;

But if you want to see the dates where there no questions or answers, you'll need to use the NUMBERS table trick because MySQL doesn't have recursive functionality -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-01-02" with your respective start and end dates (but use the same format, YYYY-MM-DD HH:MM:SS) -

   SELECT x.dt
     FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) DAY )) AS dt
             FROM numbers n
            WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) DAY) <= '2010-12-31' ) x
   

4. LEFT JOIN onto your table of data based on the datetime portion.

   SELECT x.dt,
          COALESCE(COUNT(a.userid), 0) as new_users, 
          COALESCE(COUNT(a.question), 0) as questions_sent, 
          COALESCE(COUNT(a.answer), 0) as answers_sent
     FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) DAY )) AS dt
             FROM numbers n
            WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) DAY) <= '2010-12-31' ) x
   LEFT JOIN YOUR_TABLE a ON a.regdate = x.dt
    GROUP BY x.dt
    ORDER BY x.dt
   

Why Numbers, not Dates?

Simple - dates can be generated based on the number, like in the example I provided. It also means using a single table, vs say one per data type.