samedi 26 septembre 2015

SQLite query; Group By Hour And By Device

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