[RESOLVED] SQL Server '05: Query Browser Usage stats

[aconybeare]

Hi,

I'm trying to write a query to get the browser usage stats on my site. I know that these are very difficult to get right but I'm looking for the best that I can have based on the information I'm collecting.

I have the following fields in an activities table. A row is inserted each time a user successfully logs in (I'm using browser hawk to extract the browser specific information)

Act_Id, User_Id, User_Name, Application, Act_Date, HTTP_User_Agent, IP_Address, Platform, Browser, Browser_Full_Ver, Browser_Major_Ver, Browser_Minor_Ver

This is what I have so far -

SELECT Browser, Browser_Major_Ver, COUNT(*) AS Usage_Count
FROM Activity
WHERE Browser IS NOT NULL
AND Application NOT LIKE '%EO%'
AND Act_Date BETWEEN CONVERT(CHAR(8),DateAdd(d,+1,DateAdd(m,-6,GETDATE())),112) AND CONVERT(CHAR(8),GETDATE(),112) 
GROUP BY Browser, Browser_Major_Ver
ORDER BY Usage_Count DESC, Browser, Browser_Major_Ver

Does anyone have any ideas on how to remove duplicates. Something like count only once if -
IP address, Browser and Browser_Full_Ver occur multiple times
Or
User_Id, Browser and Browser_Full_Ver occur mulitple times

Regards Al

[NeedSomeAnswers]

Well you could use DISTINCT as in;

Select Distinct User_Id, Browser and Browser_Full_Ver occur,COUNT(*) FROM Activity Group By Distinct User_Id, Browser and Browser_Full_Ver

However Distinct will only eliminate duplicates where all the columns in the Select are the same.

[aconybeare]

NeedSomeAnswers,

Thanks for your reply, unfortunately that returns the user id in the results which I don't want

[kevchadders]

You could try putting a built string into your COUNT of those 3 fields you want to remove duplicates on.
(Bit like your creating a primary key in the COUNT function to get a distinct count on)

eg. For counting once when IP address, Browser and Browser_Full_Ver occur multiple times.

Try

SELECT	Browser, 
		Browser_Full_Ver, 
		IP_Address, 
		COUNT(DISTINCT IP_address + Browser + Browser_Full_Ver) AS Usage_Count
FROM Activity
WHERE Browser IS NOT NULL
AND Application NOT LIKE '%EO%'
AND Act_Date BETWEEN CONVERT(CHAR(8),DateAdd(d,+1,DateAdd(m,-6,GETDATE())),112) AND CONVERT(CHAR(8),GETDATE(),112) 
GROUP BY IP_Address, Browser, Browser_Full_Ver
ORDER BY Usage_Count DESC, IP_Address ,Browser, Browser_Full_Ver

ps. I change Browser_Major_Ver to Browser_Full_Ver in my version to help with the GROUP and ORDER BY.

[NeedSomeAnswers]

Right well you could use a temp table or table variable.

Do your initial Select into a @TableVariable so that you get your non duplicated data in here.

Then do a second Select off your @TableVariable excluding you UserID or any other columns you don't want.

Have you used Table Variables before ?

Edit - Or just do what kevchadders says above !!

[aconybeare]

KevChadders and NeedSomeAnswers,

Thanks for your replies, both helped me come up with a solution: Use a derived table -

SELECT b.Browser, b.Browser_Major_Ver, COUNT(*) AS Usage_Count
FROM (
		SELECT DISTINCT User_Id, IP_Address, Browser, Browser_Full_Ver, Browser_Major_Ver 
		FROM Activity
		WHERE Browser IS NOT NULL
		AND Application NOT LIKE '%EO%'
		AND Act_Date BETWEEN CONVERT(CHAR(8),DateAdd(d,+1,DateAdd(m,-6,GETDATE())),112) AND CONVERT(CHAR(8),GETDATE(),112)
	) b
GROUP BY b.Browser, b.Browser_Major_Ver
ORDER BY Usage_Count DESC, b.Browser, b.Browser_Major_Ver

I know this isn't perfect because if a user has DHCP turned on they are likely log in with multiple IP addresses and so get counted multiple times. If I just use the user id and they have multiple computers running the same browser then they will only get counted once.