CODEWITHSUNDEEP
sqlsql-serverdatetimecount
PZ01
PZ01

Reputation: 23

Pulling Datetime when using COUNT(UserID)

I have the tables Users

UserID     FirstName     LastName     Email
------     ---------     --------     -----
1          Fred          Smith        [email protected]
2          Bob           Hill         [email protected]
3          Jane          Doe          [email protected]

and LoginSession

LoginSessionID     UserID     StartDate
--------------     ------     ---------
1                  1          2014-11-23 08:37:14.836
2                  1          2014-11-25 11:13:53.225
3                  2          2014-12-01 03:15:33.846
4                  1          2014-12-01 17:34:19.036
5                  3          2014-12-05 12:55:01.998
6                  1          2014-12-14 17:20:14.636
7                  3          2014-12-15 10:02:17.376

What I am trying to do is find the users who have logged on only once and find out when that was.

I have managed to find the users who have logged on only once by using

SELECT 
    U.FirstName, U.LastName, COUNT(L.UserID) AS Visits
FROM 
    LoginSession L
JOIN 
    Users U ON U.UserID = L.UserID
GROUP BY 
    U.FirstName, U.LastName
HAVING 
    COUNT(L.UserID) = 1

But I also want to pull through the L.StartDate of those users. If I add it to the select query I get an error because it's not contained in an aggregate function or GROUP BY clause. If I add it the the GROUP BY line (to avoid that error) I get each and every login handily marked as 1 visit!

I also tried using a subquery but I got an error because it returned more than one result.

I really am totally stumped!

Upvotes: 0

Views: 30

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use windowed version of COUNT:

SELECT FirstName, LastName, StartDate
FROM (
  SELECT U.FirstName, U.LastName, L.StartDate,
         COUNT(*) OVER (PARTITION BY U.UserID) AS cnt
  FROM LoginSession L
  JOIN Users U ON U.UserID = L.UserID ) AS t
WHERE t.cnt = 1

COUNT with OVER clause will return the number of records per U.UserID. Using an outer query you can fetch exactly these records.

Demo here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can do this with aggregation:

select UserId, min(StartDate) as StartDate
from LoginSession ls
group by UserId
having count(*) = 1;

The min() returns the value you want, because there is only one row that matches. You can use an addition join to get additional information about the users. 

select u.*, lsu.StartDate
from Users u join
     (select UserId, min(StartDate) as StartDate
      from LoginSession ls
      group by UserId
      having count(*) = 1
     ) lsu
     on lsu.UserId = u.UserId;

Upvotes: 1

Related Questions