If you require a SQL-only solution, there may be one (I can imagine a 4- or
5-pass process that might get you the numbers you want). However, I agree
with the other respondents to your message, it will not be as fast, as
efficient, or as easy to write as one written in client-side code. IMHO,
the ability to step through the records and compute statistics on a
row-by-row basis is just too good a fit to justify spending the time to
develop a "batch" solution.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
John Mistler
<[EMAIL PROTECTED] To: Roger Baklund <[EMAIL
PROTECTED]>, <[EMAIL PROTECTED]>
phia.net> cc:
Fax to:
06/28/2004 04:17 Subject: Re: SQL challenge
PM
Wow, that was it! I changed the WHERE to (because I wasn't clear):
. . . WHERE theDate BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND
CURDATE() . . .
Now, I have just one more that I still am stumped by, if anyone (Roger or
other) has a second:
Given theTable with 2 columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT)
and theDate (DATETIME)
I need to find:
1. The average TIME ELAPSED between consecutive REPEATED instances of the
rowID (GROUP BY rowID, I assume) between one month ago and now.
- So, if there are 3 rows with rowID = 1 and 2 rows with rowID = 2, it
would
return the average time (total seconds, or HH:MM:SS) of ((time elapsed
between row1 and row2 where rowID = 1, row 2 and row 3 where rowID = 1) AND
(time elapsed between row1 and row2 where rowID = 2)). *Note that it would
not use the time elapsed between row 1 and row 3 where rowID = 1) for the
average calculation.
2. The average time elapsed between REPEATED instances of the rowID PER
WEEK
between one month ago and now. (This one might be as easy as using the
WEEK() function as before . . .)
Thanks,
- John
on 6/28/04 2:37 AM, Roger Baklund at [EMAIL PROTECTED] wrote:
> * John Mistler
>> I have a difficult query for you that has me stumped. The table has two
>> columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate
>> (DATETIME).
>> I need it to find:
>>
>> 1. The COUNT of REPEATED instances of the rowID in the last month.
>>
>> - so if there are 5 rows with the same rowID in the last month, it would
>> return "4" (I can only seem to get it to return "10" WHERE t1.rowID =
>> t2.rowID AND t1.theDate > t1.theDate)
>
> I'm not sure if I understand, but have you tried something like this:
>
> select rowID,COUNT(*) AS cnt
> from theTable
> where month(theDate) = month(curdate())
> group by rowID
> having cnt>1;
>
> If you by "last month" meant the last in the dataset, you could find the
> month by issuing:
>
> select @m:=month(max(theDate)) from theTable;
>
> You say you want the answer "4" when the count is 5...? You can subtract
one
> from the count in the query:
>
> select rowID,COUNT(*)-1 AS cnt
> from theTable
> where month(theDate) = @m
> group by rowID
> having cnt>0;
>
>> 2. The AVERAGE number of REPEATED instances of the rowID per week
(Monday
>> 00:00:00 through Monday 00:00:00 one week later) in the last month.
>
> You want to group by week, you can get the week using the week()
function.
> For weeks starting on monday, the second parameter should be 1. You want
the
> average of the counts... try using a temporary table, something like
this:
>
> create temporary table tmp1 select
> week(theDate,1) AS week, rowID, count(*)-1 AS cnt,
> from theTable
> where month(theDate) = @m
> group by week,rowID
> having cnt>0;
> select week,avg(cnt) from tmp1 group by week;
>
> --
> Roger
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
