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

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 -

Code:

---

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

Well you could use DISTINCT as in;

Code:

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.

NeedSomeAnswers,

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

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

Code:

---

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.

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 !!

KevChadders and NeedSomeAnswers,

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

Code:

---

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.