Jake,
You will need to do direct dbms command to do this and you will need a
couple of views to do this.
First lets get a list of all of our first pages hit for each session and put
it in view first_hit
create view first_hit (hitid,sessionid) as select (min(hitid)), sessionid
from hits t1 where page = 1 group by sessionid
Now we want to pick up the next pageid hit after the first one for each
session
create view second_hit (sessionid, second_hitid) as
select sessionid,(min(hitid)) from hits t1, first_hit t2 where t1.sessionid
= t2.sessionid and t1.hitid <> t2.hitid
Now you can get the page that was hit from a directdbms action
select pagid from hits t1, second_hit t2 where t1.sessionid = t2.sessionid
and you can now also count it and group it.
select pagied, count (*) from hits t1, second_hit t2 where t1.sessionid =
t2.sessionid group by pageid
This should spit out the results you are looking for.
Troy Sosamon
===== Original Message from [EMAIL PROTECTED] at 7/19/02 5:11 pm
>Hi Everyone,
>
>I have to write a query that I am having troubles coming up with the code
>for and I thought this might be a good place to turn. I have a table storing
>specific "page hit" data that includes: sessionid, pageid, userid and
>timestamps. What I need to do is find which page was visited immediately
>after visiting pageid 1 for each session in the table (if a hit is recorded
>for pageid 1 in that session). As well, I need the results grouped to
>display a count of how many times each of the pages hit after pageid 1 was
>hit.
>
>Even a little insight or direction in writing this query could go a long way
>for me.
>
>Thanks in advance,
>
>Jake Stevens
>
>
>Here is what I need the query results to look like:
>
>PageID Count(PageID)
>--------------------------
> 30 3
> 31 1
>--------------------------
>2 matching records.
>
>The table Hits is made up of the following columns that represent a "page
>hit":
> HitID = an auto-incremented key for each record in the table
> SessionID = another key that holds a unique number which represents a
>Witango user session that expires in 30 minutes
> PageID = each web page in the application has a unique numeric pageid
> UserID = each user has a unique userid
>
>And without further adue, the data:
>-----------------------------------------------------------------
>
>SQL Server Create table:
>
>CREATE TABLE hits(
> HitID integer PRIMARY KEY,
> SessionID integer NOT NULL,
> PageID integer NOT NULL,
> UserID integer NOT NULL,
> Timestamp datetime NOT NULL
>);
>
>MySQL create table:
>
>CREATE TABLE `hits` (
> `HitID` int(11) NOT NULL auto_increment,
> `SessionID` int(11) NOT NULL,
> `PageID` int(11) NOT NULL,
> `UserID` int(11) default NULL,
> `Timestamp` datetime NOT NULL,
> PRIMARY KEY (`HitID`)
>) TYPE=MyISAM
>
>
>INSERT INTO Hits VALUES (1,1,1,1,'2002-01-01 00:00:10') ;
>INSERT INTO Hits VALUES (2,1,30,1,'2002-01-01 00:00:20') ;
>INSERT INTO Hits VALUES (3,1,23,1,'2002-01-01 00:00:30') ;
>INSERT INTO Hits VALUES (4,1,11,1,'2002-01-01 00:00:40') ;
>INSERT INTO Hits VALUES (5,2,1,1,'2002-01-01 00:45:11') ;
>INSERT INTO Hits VALUES (6,2,30,1,'2002-01-01 00:45:21') ;
>INSERT INTO Hits VALUES (7,2,22,1,'2002-01-01 00:45:31') ;
>INSERT INTO Hits VALUES (8,2,10,1,'2002-01-01 00:45:41') ;
>
>INSERT INTO Hits VALUES (9,3,1,2,'2002-01-01 00:01:10') ;
>INSERT INTO Hits VALUES (10,3,30,2,'2002-01-01 00:01:20') ;
>INSERT INTO Hits VALUES (11,3,22,2,'2002-01-01 00:01:30') ;
>INSERT INTO Hits VALUES (12,3,10,2,'2002-01-01 00:01:40') ;
>INSERT INTO Hits VALUES (13,4,1,2,'2002-01-02 00:00:10') ;
>INSERT INTO Hits VALUES (14,4,31,2,'2002-01-02 00:00:20') ;
>INSERT INTO Hits VALUES (15,4,23,2,'2002-01-02 00:00:30') ;
>INSERT INTO Hits VALUES (16,4,11,2,'2002-01-02 00:00:40') ;
>
>
>
>// end
>
>________________________________________________________________________
>TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
________________________________________________________________________
TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
with unsubscribe witango-talk in the message body
