Click here to Skip to main content
15,169,285 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.... ?
Updated 20-Oct-15 1:00am
Kornfeld Eliyahu Peter 20-Oct-15 5:14am
You probably meant count(SessionID), as ID is a meaningless SID...
Wombaticus 20-Oct-15 6:01am
It doesn't matter as I only want to count rows - but really 'SessionID' shouldn't have been in that 1st SQL query at all - I've removed it now

1 solution

I would do that this way:

SELECT SUM(CountOfID) AS Total, AVG(TimeDifference) AvgTime
   SELECT count(ID) AS CountOfID, timediff(SECOND, max(ddateTime),min(dDateTime)) AS TimeDifference
   FROM tableName
   WHERE date(dDateTIme) >= 'some start date' and date(dDateTIme) <= 'some end date'
   GROUP BY SessionID order by SessionID )AS T

For further information, please see:
MySQL AVG TimeDiff of Multiple rows[^]
Avg Time difference in mysql[^]
Wombaticus 22-Oct-15 17:09pm
Thank you! (For the record, timediff only accepts two parameters, and you missed and AS in 'AVG(TimeDifference) AvgTime', but still you got me there! :)
Maciej Los 22-Oct-15 17:11pm
You're very welcome.
Cheers, Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900