CODEWITHSUNDEEP
sqlite
GentlemanS
GentlemanS

Reputation: 121

Group by Difference Between Rows SQLITE

I have a table values with following format, I would a SQL query that will count the differences in time for the Clock the column delta clock does not exist, but is added to make the example easier to follow

RID | Clock | delta clock ( result of lead(Clock,1) OVER(ORDER BY Clock) - Clock )
0     0       2
1     2       4
2     6       2
3     8       2
4     10      4
5     14      2
6     16      2
7     18      NULL

What I'm Looking for should be something like

Select lead(Clock,1) OVER(ORDER BY Clock) - Clock AS "Step" , count(*)
From values
Group by Step

And would return

Step | count
2      5
4      2
Null   1

Upvotes: 2

Views: 212

Answers (1)

forpas
forpas

Reputation: 164089

Assuming that the values of the column Clock are always increasing, you can aggregate after you get the differences:

SELECT Step, COUNT(*) Count
FROM (
  SELECT LEAD(Clock) OVER(ORDER BY Clock) - Clock AS Step
  FROM [values]
)
GROUP BY Step
ORDER BY Step IS NULL, Step

See the demo.
Results:

| Step | Count |
| ---- | ----- |
| 2    | 5     |
| 4    | 2     |
|      | 1     |

Upvotes: 2

Related Questions