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
