[thelist] SQL Question

Roger Ly rogerly at bareviking.com
Thu Jun 27 16:35:07 CDT 2002


I'm trying to anayze some data we store about browsers our users use to visit
our site, but I am having a problem querying a good set of data.

We have a table that logs everytime a user logs in with their UserAgent and
their userID (as well as a timestamp, IP address, and other such things).

I want to find out the number of times people login to our system with each
UserAgent.  So far, I have a query like this:

SELECT COUNT(useragent), useragent FROM tblUserSessions
GROUP BY userAgent
ORDER BY useragent

Which does what you'd expect to do -- return each UserAgent, and the number of
times it was used to log in to our system.

But, since I am trying to see how many users logs in with, say Netscape, I want
to filter out all duplicate user logins with the same UserAgent.  So, if a user
logs in twice with Netscape 4.73, I want to log that as one Netscape 4.73, but
if a user logs in once with Netscape 4.51 and once with IE 6.01, I want to log
it as one for each.

This sounds pretty simple to me, but I can't seem to get it to return the
correct results.

Let's just assume tblUserSessions is just (userID, userAgent)

Any SQL gurus out there that can help?

TIA,
Roger



More information about the thelist mailing list