Thanks, Shawn, this helped a lot, although I'm not there yet. (..and you got it absolutely right, I just couldn't explain it correctly).
I tried to execute this in OpenOffice, but it seems to use a function called executeQuery(). I don't know if it's part of OpenOffice or a library routine in the JDBC it uses, but I get an error saying that data manipulation statements cannot be issued using executeQuery(), so the query you suggested fails. Working directly with the database (using MySQLcc or the console) works fine. ...but at least I now have a clue of how it works, so I'll try to make a workaround in OOo. Thnx! /Marty On Monday 13 December 2004 22:40, [EMAIL PROTECTED] wrote: > It's not a hard as you are making it out to be. You are already able to > "Group" you data on a value that breaks your data into "useful chunks". In > your case you already figured out one way to differentiate one hour from > another and one weekday from another. (The value 'Sun 00' is different > than 'Mon 00'). What I don't think you knew how to explain was that you > wanted to average each hour of each day across separate weeks. > > There could be a way to do this in one query but I am not certain how so I > will explain a two-step method: > > First collect the same data you are already generating (totals by > hour/weekday) except also collect a week number (so that we collect > separate values for each week). Store those values somewhere temporarily > (may I suggest a temp table?) > > CREATE TEMPORARY TABLE tmpStats > SELECT DATE_FORMAT(incall, '%V') AS week , DATE_FORMAT(incall, '%a %H') AS > DayHour, COUNT(*) AS Calls FROM > incoming WHERE DATE_FORMAT(incall, '%Y %m %d') >= '2004 12 01' GROUP BY > DayHour > > Now it's simple to average across the weeks: > > SELECT DayHour, avg(calls) > FROM tmpStats > GROUP BY DayHour; > > (Group By has an implicit ORDER BY built into it. You have to specifically > ask for that sorting to NOT happen if you don't want it). Now that you no > longer need it, you can also drop the temp table: > > DROP TEMPORARY TABLE tmpStats; > > HTH, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Onemarty <[EMAIL PROTECTED]> wrote on 12/13/2004 11:30:36 AM: > > For me, a Newbie, this is a tricky one. I've been through the manual and > > this > > > archive, but haven't found a solution. Could someone of you > > experienced please > > help? > > > > Is it possible to make this query in one single SQL-statement? (Using > > MySQL > > > 4.0.15 - If this can be done smarter in 4.1.x, please shed some light on > > that > > > too as we might upgrade one day). > > > > A timestamp for each incoming call is stored in 'incall' of type > > Datetime in > > > table 'incoming'. > > > > With number of calls grouped on the hour I need to follow up: > > > > Average of (No. of incoming calls)/hour/weekday. > > > > The following command does half ;-) of the job: > > > > SELECT DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM > > incoming WHERE DATE_FORMAT(incall, '%Y %m %d') >= '2004 12 01' GROUP BY > > > > DayHour ORDER BY DATE_FORMAT(incall, '%w %H'); > > > > This gives me a result like this: > > > > +---------+-------+ > > > > | DayHour | Calls | > > > > +---------+-------+ > > > > | Sun 00 | 809 | > > | Sun 01 | 638 | > > | Sun 02 | 573 | > > | Sun 03 | 400 | > > | Sun 04 | 315 | > > | Sun 05 | 269 | > > | Sun 06 | 245 | > > | Sun 07 | 314 | > > | ... | ... | > > > > etc. > > > > But these are the totals/hour/weekday, and I need the > > average/hour/weekday. > > > What I haven't been able to figure out from the manual nor this > > archive is how > > to calculate the average, (or how to find out the right divisor for each > > > > 'Calls'-value). > > I tried various ways to use AVG() but none worked. As a test, I also > > tried > > > 'COUNT(*)/3' and it works, but how can I replace that number 3 with a > > variable 'n' that has the correct value. > > > > Example: > > On Dec13th at 14:00 hours I want a statistic snapshot from Dec 01 until > > now. > > > That means that all Calls-values from Wed 00 to Mon 13 should be divided > > by > > > 2, but the values from Mon 14 to Tue 23 should be divided by 1 to get > > the > > > correct average value. > > > > Thanks in advance > > > > Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
