[thelist] extracting site "visits" from a table tracking page views (mysql)

[Sarah Sweeney]

> This may not be the most efficient way to do this, but it might get you started:
> 
> SELECT
> 	a.UserID,
> 	COUNT(a.RecordID) as UserVisits,
> 	a.Country
> FROM
> 	Stats_Pages AS a
> WHERE EXISTS
> 	(
> 	SELECT	
> 		NULL
> 	FROM
> 		Stats_Pages AS b
> 	WHERE
> 		a.UserID = b.UserID
> 	AND
> 		DateDiff(hh, b.TimeStamp, a.TimeStamp) >= 4
> 	AND
> 		b.RecordID = 
> 		(
> 			SELECT 
> 				Max(c.RecordID)
> 			FROM
> 				Stats_Pages c
> 			WHERE
> 				b.UserID = a.UserID
> 			AND
> 				c.RecordID < a.RecordID
> 		)
> GROUP BY  
> 	a.UserID
> 	a.Country
> ORDER BY  
> 	COUNT(a.RecordID) DESC

This looks like it's worth a try for sure (thanks, Ken). Otherwise I'll 
probably have to, as others have suggested, do some processing of the 
data and add another table. I was hoping to avoid this if possible. I'll 
let you know if this works.

-- 
Sarah Sweeney
Web Developer & Programmer
Portfolio :: http://sarah.designshift.com
Blog, etc :: http://hardedge.ca