sqlite add tag
DeFacto
Hello,

Is it possible, to separate the records into portions and return the sum of each portion in SQLITE?
Let's say i have a table

```
CREATE TABLE myTable (`Start` TIME, `END` TIME, `Cnt`);
INSERT INTO myTable (`Start`, `END`, `Cnt`) VALUES
  ('22:04:02', '22:04:12', 8),
  ('22:04:12', '22:04:22', 22),
  ('22:04:22', '22:04:32', 10),
  ('22:04:32', '22:04:42', 9),
  ('22:04:42', '22:04:52', 92),
  ('22:04:52', '22:05:02', 14),
  ('22:05:02', '22:05:12', 88),
  ('22:05:12', '22:05:22', 24),
  ('22:05:22', '22:05:32', 30);
```
What i am trying to achive is to get the `SUM`of `Cnt` values, by e.g. 20 seconds, between specific time interval.
```
SELECT SUM(Cnt) FROM myTable WHERE START >= '22:04:32' AND END <= '22:05:22' 
```
will return the sum of all records between spcified time, but the desired result would be:
| Time | Sum |
|:----|:----|
| 22:04:52|101|
| 22:05:12|102|
| 22:05:22|24|

[fiddle](https://dbfiddle.uk/aMVhiefO)


Top Answer
Luuk
Your fiddle was from 3.27, Since that some date/time function got improved

```
SELECT time(datetime(Start - Start % 20,'unixepoch')) as time,
       sum(Cnt) as sum
FROM (
  SELECT 
    unixepoch(DATE()||' '||Start) as Start,
    unixepoch(DATE()||' '||END) as END,
    Cnt
  FROM myTAble
)
GROUP BY time(datetime(Start - Start % 20,'unixepoch'))
```

|time	|sum|
|-|-|
|22:04:00	|30|
|22:04:20	|19|
|22:04:40	|106|
|22:05:00	|112|
|22:05:20	|30|


see: https://dbfiddle.uk/Fw3I0QZA

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.