Combining count and avg functions in SQL (MySQL)

Question

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 19-Oct-15 23:01pm

Wombaticus

Updated 20-Oct-15 0:00am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2015-10-22T09:01:00

Solution 1

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[^]

Posted 22-Oct-15 9:01am

Maciej Los

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