SO I'm experimenting with my own site analytics feature (I can't stand Google Analytics any longer...), and have a databse table that recordsr user activity.
ID - autoincrement
SessionID (Int)
dDateTime (Date/time of page hit)
plus a few other things that don't matter here.
Now, I can get a basic listing of sessions using this:
select count(ID), timediff(max(ddateTime),min(dDateTime))
from tableName
where date(dDateTIme) >= 'some start date'
and date(dDateTIme) <= 'some end date'
group by SessionID order by SessionID
OK... but what I want is the averages - i.e. a single row that tells me the average number of page hits per session, and the average length of time the user spent on the site. Something like:
select avg(count(ID)), avg(timediff(max(ddateTime),min(dDateTime)))
from tableName
where date(dDateTIme) >= 'some start date'
and date(dDateTIme) <= 'some end date'
group by SessionID
But that (apparently) is an invalid use of GROUP BY ....
I know it can be done.... ?