Can I optimize this sql Query?

Question

Below is a sample query of what I am trying to do and it gets the job done but I feel like the sub queries are not the best way to go here. Any pointers?

SELECT DISTINCT
    u.UserID,   
    (SELECT COUNT(LoginID) FROM Logins WHERE Success = 1 AND UserID = u.UserID) AS Successful,
    (SELECT COUNT(LoginID) FROM Logins WHERE Success = 0 AND UserID = u.UserID) AS Unsuccessful,        
    (SELECT TOP 1 LoginDate FROM Logins WHERE UserID = u.UserID ORDER BY LoginDate DESC) AS LastLogin
FROM 
    Users u INNER JOIN 
    Logins l ON u.UserID = l.UserID

BTW the above example doesn't look like it needs the join but in the real solution I do to get some other columns...

Answer 1

Am I missing something significant, or could you not do this with a single aggregate query?

SELECT u.UserID,
    SUM(CASE WHEN Success = 1 THEN 1 ELSE 0 END) AS Successful,
    SUM(CASE WHEN Success = 0 THEN 1 ELSE 0 END) AS Unsuccessful,
    MAX(LoginDate) AS LastLogin
FROM Users u
INNER JOIN Logins l on u.UserID = l.UserID
GROUP BY u.UserID

Answer 2

You can try if this is more efficient in your situation:

select
  u.UserID,   
  sum(case when l.Success = 1 then 1 else 0 end) as Successful,
  sum(case when l.Success = 0 then 1 else 0 end) as Unsuccessful,
  max(LoginDate) as LastLogin
from 
  Users u
  inner join Logins l on l.UserID = u.UserID
group by
  u.UserId

Answer 3

I would try something like this:

declare @Users Table (UserID int)
declare @Logins Table (LoginID int, UserID int, LoginDate DateTime, Success Bit)

Insert into @Users
select 1 union select 2

insert into @Logins
select 1, 1, '2010-10-13 6:00:00', 1
union
select 2, 1, '2010-10-13 7:00:00', 0
union
select 3, 1, '2010-10-13 8:00:00', 1
union
select 4, 2, '2010-10-13 6:00:00', 0
union
select 5, 2, '2010-10-13 7:00:00', 1
union
select 6, 2, '2010-10-13 9:00:00', 1
union
select 7, 2, '2010-10-13 10:00:00', 1

Select    UserID,
          [1] As Successful,
          [0] As Unsuccessful,
          LoginDate As LoginDate
From (
    SELECT
        u.UserID, 
        l.LoginID,
        l.Success,
        Max(LoginDate) Over (Partition By u.UserID) As LoginDate
    FROM @Users u 
        INNER JOIN @Logins l ON u.UserID = l.UserID
) Data Pivot (
    Count(LoginID) For Success In (
        [0], [1]
    )
) Result