Difference between times for each ID

[Tony]

Hello guys I need help cause I’m not really an expert with complex queries in MySql… What I need is this:

I am developing a small analytics system… what I have is one table like this

±-----------±------------±-----------±------------±-----------+
| ID | IP | page | lastpage | time |
±-----------±------------±-----------±------------±-----------+
| 1 | x.x.x.x | … | … | 0001 |
±-----------±------------±-----------±------------±-----------+
| 2 | x.x.x.x | … | … | 1111 |
±-----------±------------±-----------±------------±-----------+
| 3 | x.x.x.x | … | … | 1113 |
±-----------±------------±-----------±------------±-----------+
| 3 | x.x.x.x | … | … | 1114 |
±-----------±------------±-----------±------------±-----------+
| 3 | x.x.x.x | … | … | 1120 |
±-----------±------------±-----------±------------±-----------+
| 2 | x.x.x.x | … | … | 1114 |
±-----------±------------±-----------±------------±-----------+
| 1 | x.x.x.x | … | … | 0004 |
±-----------±------------±-----------±------------±-----------+

Ok ID is the session id it’s one for each user… IP, page and lastpage are not relevant… what i’m interested in is the difference between the min time and the max time for each distinct ID… how do I do that?

example:

±-----------±------------±-----------±------------+
| ID | MIN | MAX | DIFF |
±-----------±------------±-----------±------------+
| 1 | 0001 | 0004 | 3 |
±-----------±------------±-----------±------------+
| 2 | 1111 | 1114 | 3 |
±-----------±------------±-----------±------------+
| 3 | 1113 | 1120 | 17 |
±-----------±------------±-----------±------------+

The time column is unix timestamp btw…

Thank you guys!

[phphelp]

So you need to group records by ID and use MySQL aggregate functions MAX() and MIN(). Your sql query will look like this:

SELECT ID, MAX(`time`) - MIN(`time`) AS diff FROM MyTable GROUP BY ID

[Tony]

You are the man! Thank you so much!

[Tony]

Oh I forgot one thing though… I also have to get the average of all those values returned… so for example if i have 5 rows 15, 57, 67, 54, 16 under the new ‘column’ diff now i need their average…

[Tony]

Sorry I figured it…

SELECT AVG(diff) FROM (SELECT ID, MAX(time) - MIN(time) AS diff FROM MyTable GROUP BY ID) AS NewTable

Thanks again