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..
LVL 10
fostejoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raopsnCommented:
This should give you that:

SELECT Location,
COUNT(Case when DATEDIFF(day,LastLogon,GETDATE())=0  Then 1 Else 0 End )
FROM Logins
0
MikeTooleCommented:
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
0
fostejoAuthor Commented:
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..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeTooleCommented:
Oops, that's what comes from winging it!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fostejoAuthor Commented:
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.
0
MikeTooleCommented:
I like the simplicity of raopsn's query too.
The fix for it is to use Sum() rather than Count() to aggregate the values.
0
raopsnCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.