SQL unique query -- need help

Question

Hello,

Need some help with what is probably a pretty basic SQL query. I'm trying to select the number of unique records that match a specific where clause.

Essentially, I'm looking to find the number of unique users that logged in between date x and y. If a user logged in more than once between those dates, it would need to only count once.

The data looks something like this:

id | user_id | lastLogin
1  | 100     | 2010-06-23 10:00:00
2  | 101     | 2010-06-23 10:05:00
3  | 100     | 2010-06-23 11:00:00 

Thanks!

Answer 1

Use the Distinct SQL operator.

Example

select distinct user_id from UsersLoggedIn where lastLogin >= '2010-06-23 10:00:00' and lastLogin <= '2010-06-23 11:00:00'

You could wrap that in a count to get the count of users.

select count(*) from (select distinct user_id from UsersLoggedIn where lastLogin >= '2010-06-23 10:00:00' and lastLogin <= '2010-06-23 11:00:00') x

Answer 2

Try:

select count(distinct user_id)
from login_table
where lastlogin between @startdatetime and @enddatetime

Answer 3

You can do something like

SELECT COUNT(DISTINCT user_id) FROM table WHERE lastLogin BETWEEN ....

Answer 4

Here's another option

SELECT COUNT(user_id), user_id, MAX(lastLogin)
FROM login_table
WHERE lastlogin BETWEEN @start AND @end
GROUP BY user_id

This will get you a login count, and the last login date as well.