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

Question

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?

Answer 1

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.

Answer 2

SELECT *
FROM   (SELECT COUNT(*) AS Last3Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 3 DAY) a,
       (SELECT COUNT(*) AS Last7Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 7 DAY) b,
       (SELECT COUNT(*) AS Last21Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 21 DAY) c