Sqlite Query To Get Sum_of_fields For Every 10mins

Hi i have the following table structure. RESE_ID DATE_OF_SERVICE TIME_OF_SERVICE NO_OF_ATT_MALE NO_OF_ATT_FEMALE 1 2012-09-18 16:17:52 5 1

Solution 1:

SELECT DATE_OF_SERVICE,
       SUM(NO_OF_ATT_MALE),
       SUM(NO_OF_ATT_FEMALE),
       SUBSTR(TIME_OF_SERVICE, 1, 4) || '0 - ' ||
       SUBSTR(TIME_OF_SERVICE, 1, 4) || '9'       AS TIME_SLOT
FROM mytable
GROUP BY 4

This will result in an output like this, i.e., no records for empty time slots:

DATE_OF_SERVICE  SUM(NO_OF_ATT_MALE)  SUM(NO_OF_ATT_FEMALE)  TIME_SLOT
---------------  -------------------  ---------------------  -------------
2012-09-18       6                    3                      16:10 - 16:19
2012-09-18       2                    1                      16:20 - 16:29
2012-09-18       1                    4                      16:30 - 16:39
2012-09-18       6                    5                      16:40 - 16:49
2012-09-18       3                    2                      17:00 - 17:09
2012-09-18       0                    1                      17:10 - 17:19