I have a SQLite database of counters and counts like this:
DateTime |Gate|Count
-----------------------+----+-----
2015-09-23T19:23:01.000| 2| 345
2015-09-23T19:41:08.000| 2| 346
2015-09-23T19:41:30.000| 3| 654
2015-09-23T19:44:00.000| 4| 556
2015-09-23T21:13:47.000| 2| 348
I would like to determine the number counted by each gate per hour. In the above table Gate #2 has a count of 1 during the 19:00 hour and a count of 2 in the 21:00 hour.
I managed to get the following select statement:
SELECT strftime('%Y-%m-%dT%H:00:00.000', DateTime),
max(Count) - min(Count)
FROM GateTbl
WHERE Gate='2'
GROUP by strftime('%Y-%m-%dT%H:00:00.000', DateTime);
It seemed at first that I was on the right track as this almost did what I want. But it only works for one gate at a time, when I want to return hourly counts for all gates. I've also come to realize that it doesn't work for hours like 21:00 above, where there is only a single entry for that hour.
I can't see a way to do this in a SQL statement and I'm hoping that someone here can. Any help is appreciated.
Aucun commentaire:
Enregistrer un commentaire