MySQL Grouping Problem - MySQL Database

raydar2000

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

Mar 14 '07 #1

Subscribe Post Reply

987

Similar topics

Grouping in SQL query

by: Debbie Davis | last post by:

Hi there, SQL 2000 I have the following query: SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor Works great, returns the sponsor and the total * 2 of their...

ASP / Active Server Pages

OLAP Proposal for MySQL

by: Philip Stoev | last post by:

Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...

MySQL Database

Transforming with XSLT, Grouping elements until difference found.

by: Jody Greening | last post by:

Transforming with XSLT, Grouping elements until difference found. I am seeking some help with the following problem, I am fairly new at XSLT transformations, and my problem may lie in looking at...

.NET Framework

Grouping over multiple nodes - possible?

by: Andreas Håkansson | last post by:

Seeing how my previous post seem to have fallen between the cracks, I thought I would have a second, more direct, go at it. So my question is "Is it possible to group (Muenchian method) over...

.NET Framework

A2K: Report formatting issue grouping on "Fld<>'Value'"

by: ahaque38 | last post by:

Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have...

Microsoft Access / VBA

Formatting within Access report text controls - OR - sorting and grouping in Word

by: Mike MacSween | last post by:

tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...

Microsoft Access / VBA

Sort (ORDER BY) by a comma-separated adjoined string of related separated fields in MySQL 4.1?

by: phillip.s.powell | last post by:

Ok, you have three tables. You're supposed to be able to not only sort (ORDER BY) according to a_name, no problem, but you must also have the ability to sort (ORDER BY) the relationship between...

MySQL Database

problem with crystal report 9 for dynamic multilevel grouping in postback of page

by: virendra | last post by:

hi, I am working on crystal report 9 in asp.net. I created multilevel dynamic grouping in CR. if page is load firsttime. and i am giving firstlevel of grouping or whatever it's working fine. but...

ASP.NET

Decode grouping error

by: Corey | last post by:

hello, I’m trying to run a query and I’m getting error messages Can anyone help me get though this problem? 1: Tried this and got this error message ORA-00923 , decode...

Oracle Database

Grouping MySQL Query Results

by: Shiriah | last post by:

I feel that this is a rather trivial concept that I cannot quite wrap my head around to make work in PHP. I consider myself an intermediate user in SQL (both MSSQL and MySQL) and somewhat novice in...

PHP

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice