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
