CODEWITHSUNDEEP
phpmysqlsqlselect
Michael Meier
Michael Meier

Reputation: 650

Get difference of two numbers in two different rows/columns with sqlite / php

I have a SQLite Database with a table called "events", it has the following structure:

rowid       ID                  startTimestamp   endTimestamp
1           00:50:c2:63:10:1a   1000             1010
2           00:50:c2:63:10:1a   1100             1030
3           00:50:c2:63:10:1a   1090             1110   
4           00:50:c2:63:10:1a   1210             1310
.
.
.

It's no problem to calculate the average time between startTimestamp and endTimestamp:

SELECT ID, avg(endTimestamp - startTimestamp) AS duration FROM events WHERE senderID ="00:50:c2:63:10:1a"

But now i want to have the average differences between the endTime and the startTime between one row and the following row, that means for my example:

1100-1010 = 90

1090-1030 = 60

1210-1110 = 100

Sum of differences = 90 + 60 + 100 = 250

Average Difference = 250 / 3 = 83,33

Is there a way to do this with an SQL Query? Or should is it necessary to write a piece of code in PHP?

Upvotes: 1

Views: 6087

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT id,( SUM(duration) / COUNT(*) ) duration
FROM (SELECT ID, (endTimestamp - startTimestamp) AS duration 
      FROM events WHERE senderID ="00:50:c2:63:10:1a")

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

SELECT
    id,
    ( MAX(endtimestamp) - MIN(startTimestamp)
    - SUM(endtimestamp-startTimestamp)
    ) / (COUNT(*)-1) AS averageDifference
FROM
    events
GROUP BY
    id ;

Tested at SQL-Fiddle: test-1 (thanks to @bonCodigo for that)

Since the MIN(endtimestamp) may not be the one with lowest rowid, the above query needs a correction:

SELECT
    id,
    ( ( SELECT endtimestamp FROM events AS ee 
        WHERE ee.id = e.id ORDER BY rowid ASC LIMIT 1 )
    - ( SELECT startTimestamp FROM events AS ee
        WHERE ee.id = e.id ORDER BY rowid DESC LIMIT 1 )
    - SUM(endtimestamp-startTimestamp)
    ) / (COUNT(*)-1) AS averageDifference
FROM
    events AS e
GROUP BY
    id ;

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Try this: not sure if you want to use abs but adjust it accordingly. In my sample I have only used your first few rows. But if you want to use different data then you may add the proper id values to the where condition:

SQLFIDDLE DEMO

select id, 
sum(abs(endtimestamp - starttimestamp))/Count(*) 
as span from table1
where id = '00:50:c2:63:10:1a'
group by id
;

Results for all ID in the sample table:

|                ID | SPAN |
----------------------------
| 00:50:c2:63:10:1a |   50 |
| 00:50:c2:63:10:2a |  100 |

Upvotes: 0

Related Questions