Search code examples
sqlmysqldate-manipulation

SQL - Retrieve values in a single query with multiple where clauses

I have a table with the following data:

UserName | LastLogin 
-------------------------------
User1    | 2010-10-25 10:05:47
User2    | 2010-10-23 11:10:27
User3    | 2010-10-12 05:39:34
User4    | 2010-10-20 12:22:11
User5    | 2010-09-17 08:41:05

I want to be able to run a query to get the number of people who have logged in in the last 3 days, last 7 days, and last 21 days (I know these numbers will overlap). I know I can get each particular value by running a query like (syntax may not be 100% correct):

SELECT COUNT(*) 
  FROM login 
 WHERE LastLogin >= DATEDIFF(NOW(), LastLogin, INTERVAL 3 DAY);

Can I run a query to return all three values in one query? Will a GROUP BY work, or can I use nested queries? Is it just as efficient to run the query three times with the different interval specified?

Solution

  • Use:

    SELECT SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS within_3,
       SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS within_7,
       SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 21 DAY) THEN 1 ELSE 0 END) AS within_21
  FROM LOGIN l

    I used CURRENT_DATE rather than NOW() because NOW() includes the time portion.