Thanks for the responses on this. I've gotten so used to using the ref
materials I have on hand that, unfortunately, I forgot to check online
manuals from time to time. (My measly sources had helped with what I needed
until now and my best source, "MySQL Cookbook", is on loan to a friend.)
I neglected to mention that sometimes the column with the speeds in them has
extra data (like "22/25 School Zone", or "School Zone 22/25"), so it took
some extra work, like adding RIGHT() and LEFT(). (Full query below...)
I had not split the data into separate columns because 1) sometimes the data
is just one number, like -3 for 3 miles under the limit, or 5 for 5 miles
over (and sometimes in other forms), 2) I often need to make sure the actual
line of data is included, so I didn't want to separate out figures. At this
point, I'm adding an extra column to the table, SpeedOver (since most of the
differences are over the speed limit). Since the data is already in the
table, using the query help I got here, I can easily drop the new data into
the new column. If I get more data (likely), I'll be able to process it with
Perl to include the extra column (easier than in queries, since Perl is so
good with regexes and can handle 3 digit speeds better).
On Monday 05 January 2004 09:37 pm, Hal Vaughan wrote:
> I've found an odd problem in queries.
>
> I have a lot of data regarding vehicle speeds in a survey. All the data is
> in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43
> miles per hour in a 55 miles per hour zone. 80/55 means we have a serious
> speed demon, going 80 in a 55 zone.
>
> I need to be able to pull out data depending on speeds in relation to the
> speed limit, for example, find out how many are less than 55/55 or 45/45,
> or how many are more than 5 miles over (like 61/55 or 32/25).
Here's what I used as a query to create 2 columns, the first is the actual
speed info and the 2nd is the difference between the speed limit and clocked
speed. I do not, in this case, take into account 3 digit speeds. In this
example, I'm pulling out all records where the speed exceeded the speed limit
by 20 miles per hour (since that is legally reckless driving, and one data
point we're looking for):
SELECT Speed, RIGHT(SUBSTRING_INDEX(Speed, '/',
1),2)-LEFT(SUBSTRING_INDEX(Speed, '/', -1),2) AS Diff FROM TrafficSurvey
WHERE Speed REGEXP '.*[0-9]{1,2}/[0-9]{1,2}.*' AND
(ABS(RIGHT(SUBSTRING_INDEX(Speed, '/', 1),2)-LEFT(SUBSTRING_INDEX(Speed, '/',
-1),2)) > 20);
Thanks for the help!
Hal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
