[thelist] SQL Question

Ben Gustafson Ben_Gustafson at lionbridge.com
Thu Jun 27 17:36:01 CDT 2002


--
[ Picked text/plain from multipart/alternative ]

Hi Roger,

Here's your query:

	SELECT COUNT(DISTINCT userID) AS logins,
	    userAgent
	FROM tblUserSessions
	GROUP BY userAgent

The "DISTINCT userID" is the key to filtering out multiple logins by a
userID with the same userAgent. userID 123 could log in 1,000 times with the
same userAgent, and it would get counted only once.

BTW, you'll probably want to add a primary key column on your table, since
the userID/userAgent combination won't be unique.

--Ben

> From: "Roger Ly" <rogerly at bareviking.com>
> Date: Thu, 27 Jun 2002 14:34:40 -0700 (PDT)
> Subject: [thelist] SQL Question
...
> 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.
...
> Let's just assume tblUserSessions is just (userID, userAgent)
>
>
> TIA,
> Roger



More information about the thelist mailing list