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:

SQL
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:

SQL
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.... ?
Posted
Updated 20-Oct-15 1:00am
v2
Comments
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:


SQL
SELECT SUM(CountOfID) AS Total, AVG(TimeDifference) AvgTime
FROM (
   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[^]
   
Comments
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