exit pages

[NancyJ]

I have a database that contains all the pages requested on a certain site and the ip of the requester and a timestamp.
Is it possible in an sql statement to get a count of the exit pages.
ie.

count the number of times each page was the last page requested by that IP within a given time frame?

[NancyJ]

Or even, the last page each person visited...

[MRMAN]

SELECT count(*) FROM tablename GROUP BY ip

and for the last page. do you have a data time field.
if you do can't you do this

SELECT * FROM tablename WHERE ip = ip ORDER BY datatime DSC LIMIT 1

possibly. might be right might be wrong. sorry

edited: added the where statement in the query above that way yo just get the one ip address. take it out and you should get the last page for all ip addresses

[guelphdad]

For the last page visited by ip:


select
page,
ip,
timestampcolumn
from yourtablename as yt
where timestampcolumn =
(select max(timestampcolumn) from yourtablename where ip = yt.ip)


to get the count of those pages it is simply:


select
page,
count(*) as exitpages
from
(select
page
from yourtablename as yt
where timestampcolumn =
(select max(timestampcolumn) from yourtablename where ip = yt.ip)
) as dt
group by page
order by exitpages desc

[NancyJ]

select max! ofcourse, will give it a go after lunch. Thanks :D

[guelphdad]

SELECT count(*) FROM tablename GROUP BY ip

and for the last page. do you have a data time field.
if you do can't you do this

This won't work, you are grouping by ip, and you aren't selecting the page. It also won't work in that you count every time an ip has visted and not the last page visited.

SELECT * FROM tablename WHERE ip = ip ORDER BY datatime DSC LIMIT 1

that isn't going to work, what ip address are you comparing? you are only selecting from one table. you are also selecting all columns when you want to count the pages only.

[guelphdad]

select max! ofcourse, will give it a go after lunch. Thanks :D
Right, but you can't just do select max as that would give you the latest time any ip has visted the page, you want the subquery in there so you are comparing to each ip and selecting the max time for each of those ip visits.

[NancyJ]

Hmm getting an SQL error though.

select
fldPage,
count(*) as exitpages
from
(select
fldPage
from tblVisitorTracking as yt
where fldDateTime=
(select max(fldDateTime) from tblVisitorTracking where fldIP = yt.fldIP)
) as dt
group by fldPage
order by exitpages desc

#1064 - You have an error in your SQL syntax near 'select fldPage from tblVisitorTracking as yt where fldDateTime = select max(' at line 1

[guelphdad]

Okay Nancy, what that means is you are using an older version of mysql that doesn't support subqueries (you need to be on version 4.1 or newer for subqueries).

The good thing of course is that subqueries can be re-written as joins. You can use a self join on a table and compare max from one table to another.

You will need to select all the pages that are the exit pages into a temporary table.
then you can do your count from there.

First run:



create table temptable
select
yt1.page,
yt1.ip,
yt1.timestampcolumn
from yourtablename as yt1
inner join yourtablename as yt2
on
yt1.ip = yt2.ip
group by
yt1.page,
yt1.ip,
yt1.timestampcolumn
having yt1.timestampcolumn = max(yt2.timestampcolumn);



now run this:

select
page,
count(*) as total
from
temptable
group by page
order by total desc