MySQL query help with grouping and adding

Question

I have a table called user_logins which tracks user logins into the system. It has three columns, login_id, user_id, and login_time

login_id(INT) | user_id(INT) | login_time(TIMESTAMP)
------------------------------------------------------
      1       |      4       |   2010-8-14 08:54:36
      1       |      9       |   2010-8-16 08:56:36
      1       |      9       |   2010-8-16 08:59:19
      1       |      3       |   2010-8-16 09:00:24
      1       |      1       |   2010-8-16 09:01:24

I am looking to write a query that will determine the number of unique logins for each day if that day has a login and only for the past 30 days from the current date. So for the output should look like this

 logins(INT) | login_date(DATE)
 ---------------------------
     1       |  2010-8-14
     3       |  2010-8-16

in the result table 2010-8-16 only has 3 because the user_id 9 logged in twice that day and him logging into the system only counts as 1 login for that day. I am only looking for unique logins for a particular day. Remember I only want the past 30 days so its like a snapshot of the last month of user logins for a system.

I have attempted to create the query with little success what I have so far is this,

SELECT
  DATE(login_time) as login_date,
  COUNT(login_time) as logins
FROM
  user_logins
WHERE
  login_time > (SELECT DATE(SUBDATE(NOW())-1)) FROM DUAL)
  AND
  login_time < LAST_DAY(NOW())
GROUP BY FLOOR(login_time/86400)

I know this is wrong and this returns all logins only starting from the beginning of the current month and doesn't group them correctly. Some direction on how to do this would be greatly appreciated. Thank you

Answer 1

You need to use COUNT(DISTINCT ...):

SELECT
    DATE(login_time) AS login_date,
    COUNT(DISTINCT login_id) AS logins
FROM user_logins
WHERE login_time > NOW() - interval 30 day
GROUP BY DATE(login_time)

I was a little unsure what you wanted for your WHERE clause because your question seems to contradict itself. You may need to modify the WHERE clause depending on what you want.

Answer 2

As Mark suggests you can use COUNT(DISTINCT...

Alternatively:

SELECT login_day, COUNT(*)
FROM (
   SELECT DATE_FORMAT(login_time, '%D %M %Y') AS login_day,
      user_id
   FROM user_logins
   WHERE login_time>DATE_SUB(NOW(), INTERVAL 1 MONTH)
   GROUP BY DATE_FORMAT(login_time, '%D %M %Y'),
      user_id
)
GROUP BY login_day