asked on

SQL query to provide total count of 'users' logged in today for each known 'location'

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:

A, 10
B, 10
C, 0

TIA..

raopsn

This should give you that:

SELECT Location,
COUNT(Case when DATEDIFF(day,LastLogon,GETDATE())=0 Then 1 Else 0 End )
FROM Logins

MikeToole

Try this:

select L.Location, IsNull(Count(C.location) ,0)
From (select location from logins group by location) L
Left Join (Select Location,COUNT(Location) FROM Logins where DATEDIFF(day,LastLogon,GETDATE())=0 GROUP BY Location) C on L.Location = C.Location
Order By L.Location

fostejo

ASKER

raopsn & MikeToole..

raopsn - While the specified query does return all the expected Locations, the count of logins for today is wrong (they should all be zero at the moment, but the query appears to be returning all records for that location, not just those logged in today)

MikeToole - Your query errors with a 'No column was specified for column 2 of 'C''

Thanks so far guys..

ASKER CERTIFIED SOLUTION

MikeToole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fostejo

ASKER

Thanks MikeToole,

After getting rid of the erroneous bracket in the IsNull statement that works perfectly... ;)

raopsn - I like the simplicity of your query and don't understand why it isn't working as it looks logically correct! ... thanks anyway.

MikeToole

I like the simplicity of raopsn's query too.
The fix for it is to use Sum() rather than Count() to aggregate the values.

raopsn

That could be because of time part you have in the date fields
Try this...

SELECT Location,
COUNT(
Case when Convert(Char, LastLogon, 101) = Convert(Char, GETDATE(), 101) = 0 Then 1 Else 0 End )
FROM Logins