Counting Screen Resolutions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Counting Screen Resolutions

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-05 : 09:52:46

I'm capturing display resolution of course users and have found that the Javascript returns values that sometimes are the width less the scrollbar, titlebar and so on.

I want to count the users that fall into the following catagories
640x480
800x600
1024x768
1280x1024
1400x1050
1600x1200
higher

Maybe I should Update the data and using a CASE statement, store it in one of the 4 formats above.

Could the data be left raw, and a simple query constructed to provide the counts above?

Sam

Sample data follows..

select ResWidth, ResHeight, count(*) as total
from ClientStats
group by ResWidth, Resheight
order by reswidth, resheight
----------------------------------
ResWidth,ResHeight, Count(*)
-1,-1,10
640,452,2
759,556,1
759,565,1
759,572,1
767,497,1
767,547,1
767,560,1
767,567,1
767,569,1
767,571,1
767,572,37
767,600,1
768,572,1
768,990,1
776,533,1
798,572,5
798,600,2
800,490,1
800,508,1
800,540,6
800,547,1
800,566,2
800,567,1
800,568,7
800,569,2
800,570,3
800,571,2
800,572,200
800,594,1
800,598,1
800,600,28
856,452,1
963,768,2
983,732,1
983,738,1
983,740,3
991,676,1
991,705,1
991,715,3
991,728,1
991,732,1
991,734,1
991,736,1
991,738,5
991,740,45
1022,739,1
1022,740,5
1022,768,1
1024,676,1
1024,690,1
1024,695,1
1024,696,2
1024,703,1
1024,705,1
1024,707,1
1024,708,13
1024,715,10
1024,726,1
1024,728,1
1024,733,11
1024,734,14
1024,735,6
1024,736,4
1024,737,1
1024,738,9
1024,739,6
1024,740,146
1024,768,14
1024,1250,1
1119,835,1
1119,836,2
1152,770,1
1152,772,1
1152,830,1
1152,836,7
1152,864,1
1239,991,1
1247,996,4
1247,1024,1
1280,907,1
1280,932,1
1280,943,1
1280,961,1
1280,964,3
1280,989,1
1280,990,1
1280,991,2
1280,992,1
1280,994,2
1280,995,1
1280,996,11
1600,600,1
1600,1115,1
2046,740,1
2048,729,1
2048,734,1
2048,740,1

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-05 : 11:45:36

If you cross joined the categories with the clients where A.reswidth <= B.reswidth and A.ResHeight <= B.ResHeight and calculate the difference at the same time. Next take the MIN() difference that would filter out the 640x480 - 1022,739 combinations for example.
Next you could group by the categories column.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-05 : 12:04:07

I would create two tables of your possible resolutions for the Row and the Columns first:

RowValues
-------
480
600
768
1024
..etc..

ColValues
--------
640
800
1024
1280
..etc...

This way you don't have to worry about all combinations. Next, decide on your ranges for each of; that is, it must be with 50 pixels to be considered in that category.

Also, I am assuming the ClientStats table has a primary key of something like "ID".

Then you have:

SELECT C.ID, A.Rows as RowResolution, A.Cols as ColResolution
FROM
ClientStats C
INNER JOIN
(SELECT Rows, Cols FROM RowValues CROSS JOIN ColumnValues) A
ON
ABS(C.ResWidth - A.Cols) < 50 AND
ABS(C.ResHeight - A.Rows) < 50

You can get a count per resolution by using:

SELECT RowResolution, ColResolution, COUNT(*) as Number
FROM
(...above SQL..) A
GROUP BY RowResolution, ColResolution

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-05 : 12:15:48

The problem with using the CROSS JOIN that way is you can end up with a 640x1200 screen resolution, which isn't a valid combination...although a window could be sized that way, the screen is running at 1600x1200.

If you add another table:

CREATE TABLE Screens (Rows int NOT NULL REFERENCES RowValues (Rows),
Cols int NOT NULL REFERENCES ColValues (cols)
CONSTRAINT PK_Rows_Cols PRIMARY KEY (Rows, Cols) )

You can then populate it with valid screen resolution combos and use it in the same query instead of the CROSS JOIN version.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-05 : 12:42:44

i figured the cross join is just quicker and easier to write. the INNER JOIN should remove all invalid combinations.

It depends what results you are looking for. Plus, if the resolution is some weird combo because of the way the user sized the window, at least it would still be returned so you could decide how to categorize that later.

- Jeff

Edited by - jsmith8858 on 12/05/2002 12:44:57

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-05 : 12:43:57

Thank you everyone. I combined your ideas like this:

DROP TABLE ResWidths
go
CREATE TABLE ResWidths (
ResWidth INT NOT NULL
)
GO
INSERT INTO ResWidths (ResWidth) VALUES(-2)
INSERT INTO ResWidths (ResWidth) VALUES(100)
INSERT INTO ResWidths (ResWidth) VALUES(640)
INSERT INTO ResWidths (ResWidth) VALUES(800)
INSERT INTO ResWidths (ResWidth) VALUES(1024)
INSERT INTO ResWidths (ResWidth) VALUES(1280)
INSERT INTO ResWidths (ResWidth) VALUES(1400)
INSERT INTO ResWidths (ResWidth) VALUES(1600)
INSERT INTO ResWidths (ResWidth) VALUES(2048)
GO

SELECT X.Upper, Count(*) AS Total,
CAST(Count(*) * 100.0 / (SELECT COUNT(*) FROM ClientStats) AS NUMERIC(5,1)) as Percentage
FROM
(
select A.ResWidth as Upper, Max(B.ResWidth) as Lower from ResWidths A cross join Reswidths B
WHERE A.Reswidth > B.ResWidth
GROUP BY A.ResWidth
) X
INNER JOIN ClientStats CS ON CS.Reswidth <= X.Upper AND CS.ResWidth > X.Lower
Group by X.Upper

RESULTS
-------
Upper,Total,Percentage
100,10,1.4
640,2,.3
800,314,45.4
1024,319,46.1
1280,46,6.7
1600,2,.3
2048,4,.6

Edited by - SamC on 12/05/2002 12:45:09

Edited by - SamC on 12/05/2002 12:45:51

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-05 : 18:39:07

CREATE TABLE #Res
(
width int,
height int
)

CREATE TABLE #Clients
(
clientid int,
width int,
height int
)

INSERT INTO #Res (width, height) VALUES (640,480)
INSERT INTO #Res (width, height) VALUES (800,600)
INSERT INTO #Res (width, height) VALUES (1024,768)
INSERT INTO #Res (width, height) VALUES (1280,1024)
INSERT INTO #Res (width, height) VALUES (1400,1050)
INSERT INTO #Res (width, height) VALUES (1600,1200)

INSERT INTO #Clients (clientid, width, height) VALUES (1,640,452)
INSERT INTO #Clients (clientid, width, height) VALUES (2,759,556)
INSERT INTO #Clients (clientid, width, height) VALUES (3,759,565)
INSERT INTO #Clients (clientid, width, height) VALUES (4,759,565)

SELECT reswidth, resheight, Count(*) AS Total, CAST(Count(*) * 100.0 / (SELECT COUNT(*) FROM #Clients) AS NUMERIC(5,1)) as Percentage
FROM
(
SELECT A.clientID, MIN(A.awidth) clientwidth, MIN(A.aheight) clientheight, MIN(A.bwidth) reswidth, MIN(A.bheight) resheight
FROM
(
SELECT A.clientid, b.width bwidth, a.width awidth, b.height bheight, a.height aheight
FROM #Clients A CROSS JOIN #Res B
WHERE b.width >= a.width AND b.height > = a.height
) A
GROUP BY A.ClientID
) A
GROUP BY reswidth, resheight

DROP TABLE #Res
DROP TABLE #Clients

Edited by - ValterBorges on 12/05/2002 18:39:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources