Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL Grouping Problem

    Hi All,

    i am trying to generate a report that shows idle time for some vehicles... (i can do all the calcs etc but......
    I am not sure if this is possible but i need to group this data by street, however vehicles often visit the same street more than once in a day and therefore cannot group those two visits together. I need to somehow group them together while the street is the same, when it changes... its a new group and so on.

    DATA

    This is just a sample of the data, it is produced by a query that finds records where the speed is zero.

    Vehicle Date Time SpeedInst Suburb Street Latitude Longitude SpeedMax SpeedAv CourseInst CourseAv
    1000 9/2/2007 06:44:44 0 Punchbowl LUMEAH AV 33.56.184S 151.03.333E 0 0 106 106
    1000 9/2/2007 06:47:46 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 06:50:48 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106
    1000 9/2/2007 06:53:50 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106
    1000 9/2/2007 06:56:50 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 06:59:52 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:02:54 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:05:56 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:08:58 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:11:58 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:12:14 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:15:20 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106
    1000 9/2/2007 07:23:24 0 Riverwood KEATS AV 33.56.959S 151.03.158E 0 0 196 196
    1000 9/2/2007 07:23:34 0 Riverwood KEATS AV 33.56.959S 151.03.158E 0 0 196 196
    1000 9/2/2007 08:20:10 0 Bundeena HORDERN S L 34.05.037S 151.08.710E 0 0 278 278
    1000 9/2/2007 08:30:28 0 Bundeena BRIGHTON ST 34.05.084S 151.09.000E 0 0 48 48
    1000 9/2/2007 08:36:38 0 Bundeena BRIGHTON ST 34.05.084S 151.08.999E 0 0 243 243
    1000 9/2/2007 08:39:38 0 Bundeena BRIGHTON ST 34.05.084S 151.08.999E 0 0 243 243
    1000 9/2/2007 08:42:40 0 Bundeena BRIGHTON ST 34.05.084S 151.08.998E 0 0 243 243

    1000 9/2/2007 13:08:56 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:11:56 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:14:58 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:18:00 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:21:02 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:24:02 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:27:04 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:30:06 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:33:08 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:35:20 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 13:35:30 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243
    1000 9/2/2007 14:27:00 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286
    1000 9/2/2007 14:30:02 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286
    1000 9/2/2007 14:33:04 0 Riverwood NETTLETON AV 33.57.057S 151.03.190E 0 0 286 286
    1000 9/2/2007 14:36:00 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286
    1000 9/2/2007 14:51:46 0 Punchbowl LUMEAH AV 33.56.205S 151.03.421E 0 0 273 273
    1000 9/2/2007 14:54:34 0 Punchbowl LUMEAH AV 33.56.204S 151.03.422E 0 0 273 273
    1000 9/2/2007 14:56:14 0 Punchbowl LUMEAH AV 33.56.177S 151.03.355E 0 0 278 278
    1000 9/2/2007 14:58:08 0 Punchbowl LUMEAH AV 33.56.191S 151.03.341E 0 0 278 278
    1000 9/2/2007 14:58:12 0 Punchbowl LUMEAH AV 33.56.190S 151.03.341E 0 0 278 278
    1000 9/2/2007 15:41:02 0 Punchbowl LUMEAH AV 33.56.190S 151.03.344E 0 0 201 201
    1000 9/2/2007 15:41:08 0 Punchbowl LUMEAH AV 33.56.191S 151.03.344E 0 0 201 201
    1000 9/2/2007 16:24:30 0 Pyrmont PIRRAMA RD 33.52.156S 151.11.771E 0 0 15 15
    1000 9/2/2007 16:26:54 0 Pyrmont PIRRAMA RD 33.52.157S 151.11.763E 0 0 15 15
    1000 9/2/2007 16:26:58 0 Pyrmont PIRRAMA RD 33.52.158S 151.11.763E 0 0 15 15
    1000 9/2/2007 16:33:44 0 Pyrmont PIRRAMA RD 33.52.142S 151.11.766E 0 0 31 31
    1000 9/2/2007 16:33:48 0 Pyrmont PIRRAMA RD 33.52.143S 151.11.766E 0 0 31 31


    As you can see... vehicle visits LUMEAH AV twice in one day so i do't want to group these to visits together...

    Thanks

    Ray

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    So include both the street and the datetime in the GROUP BY clause.

    Is that what you mean by "group"?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    perhaps you can also include some info on the suggestions you got elsewhere on the net to help solve this problem?

  • #4
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey!

    If i group by "Street" it puts all the instances of the same street together... not what i want to do....

    Someone told me that it had something to do with run??

    Joe Celko's SQL for Smarties, 3rd edition, this type of problem is called a run and the sql you need is on page 556

    no idea.

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,041
    Thanks
    2
    Thanked 316 Times in 308 Posts
    If these are orderd by the date/time as shown, aren't they already grouped the way you want. When you process the results, just detect a change in the street name. Also, what is your ultimate goal/result you want to get from the data to help pin down what a query needs to accomplish?

    I am thinking you want to use something like the WITH ROLLUP modifier to give you a result each time the street name changes - http://dev.mysql.com/doc/refman/5.0/...modifiers.html
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi!

    Thank you for the reply!

    CFMaBiSmAd - That is what i want to do...

    I ran this query and got a NULL between all the streets...

    SELECT *,

    timediff(MAX(`vehiclehistory`.`Time`) ,MIN(`vehiclehistory`.`Time`) ) as idle

    FROM
    `vehiclehistory`
    WHERE
    `vehiclehistory`.`Vehicle` = "1000"
    AND
    `vehiclehistory`.`Date`= "2007-02-09"
    AND
    `vehiclehistory`.`SpeedInst` < "1"


    GROUP BY `vehiclehistory`.`Time`, Street WITH ROLLUP

    Is that right?

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,041
    Thanks
    2
    Thanked 316 Times in 308 Posts
    The row with the NULL in it is the row where the ROLLUP value is inserted in the result set, if that is what the question is?
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #8
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sort of... don't really know what to do with it now....

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I really don't think you want to include "Time" in your GROUP BY clause-- that would put every different time into a new group. I realize this is a contradiction to my first reply on this thread, but I think now I actually understand what it is you're trying to do

    What you really need is a new field that indicates a grouping of measurements that is unique to its group. Right now you want to differentiate 10 or so rows from another 10 or so rows, and the only thing to go off of is the datetime fields. What if a vehicle visits a street twice and ends up not visiting any other street in between? A person can look at the pattern and identify two separate visits, but it's tougher for a machine. And I realize adding a new field isn't feasible....

    If you were to group all rows with a timestamp inside the same hour, and the vehicle and street are the same of course, that would be a start, though you'll get different groups for the same visit when it goes into 2 different hours...

    Sometimes it's just easier bringing the data into your scripting language and finishing up after you've run the query. If it were me I'd simply use PHP to dump all the data into an array sorted by time, loop through the array, and check to see when the street changes.

  • #10
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you are right! PHP here we go.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •