SQL query to provide total count of 'users' logged in today for each known 'location'
Posted on 2006-04-14
I've got what I suspect will turn out to be a pretty simple query, but I've been playing around in SQL2K for longer that I want to and been unable to acheive the result I want..
Basically, I've got a table ('Logins') with 3 columns: 'User', 'LastLogon' and 'Location' and currently use the following SQL statement to get a count of the number of user logins per location so far today: "SELECT Location,COUNT(Location) FROM Logins where DATEDIFF(day,LastLogon,GETDATE())=0 GROUP BY Location ORDER BY Location"
This correctly returns those Locations where a user has actually logged in today and is fine in itself.
However, along with those locations logged in to today, I also want to list those other known locations without any logins today. For instance, if Locations A and B each have 10 logins today and Location C has 0, I want the results set to be: