I have a table structure like this.
id zoneid status datetime 1 35 IN 2016-02-02 11:36:39 2 35 OUT 2016-02-02 11:36:59 3 36 IN 2016-02-02 11:37:35 4 36 IN 2016-02-02 11:37:49 5 36 OUT 2016-02-02 11:38:06 6 38 IN 2016-02-02 11:39:37 7 38 OUT 2016-02-02 11:39:55 8 38 OUT 2016-02-02 11:40:09 9 36 IN 2016-02-02 11:41:39 10 36 OUT 2016-02-02 11:41:49 11 35 IN 2016-02-02 11:42:01 12 35 IN 2016-02-02 11:42:13 13 35 IN 2016-02-02 11:42:28 14 35 OUT 2016-02-02 11:42:39 15 36 IN 2016-02-02 11:42:49 16 36 IN 2016-02-02 11:43:03 17 36 OUT 2016-02-02 11:43:19 18 38 IN 2016-02-02 11:44:00 19 38 OUT 2016-02-02 11:44:18 20 38 OUT 2016-02-02 11:44:39 21 36 IN 2016-02-02 11:45:20 22 36 OUT 2016-02-02 11:45:43 23 35 IN 2016-02-02 11:46:00 24 35 IN 2016-02-02 11:46:04
This table contains information about a bus that starts from zone 35 and ends at zone 38(That is 1 trip). After that it starts from 38 and ends at 35(That is 2 trip), and this goes on. So, I need a query to count total number of trips (according to data, it is 4). Any suggestions? Thanks.
Note: The status column cannot be taken into account while querying.
tripid
and have another table calledtrip
. You should know what trip each bus is doing at certain times (e.g. a timetable). Having thetripid
would solve all your problems – Mark Sinkinson Jan 29 '16 at 11:10id
,datetime
,latitude
,longitude
,deviceid
. Among these,id
,datetime
might be useful according to me. – A J Jan 29 '16 at 11:24IN/OUT
column in the question and not thedatetime
one? – ypercubeᵀᴹ Jan 29 '16 at 11:27