>I have a GROUPing question on the following query:
>
>SELECT HOUR(closedtime) AS hour, COUNT(assignedto) AS count FROM db WHERE
>(closedtime >='2001-01-01' AND closedtime <= '2001-01-31 23:59:59') AND
>assignedto='person' GROUP BY hour;
>
>give something like this:
>
>+------+------------+-------+
>| hour | prettyhour | count |
>+------+------------+-------+
>| 8 | 8AM | 40 |
>| 9 | 9AM | 161 |
>| 10 | 10AM | 265 |
>| 11 | 11AM | 177 |
>| 12 | 12PM | 213 |
>| 13 | 1PM | 93 |
>| 14 | 2PM | 119 |
>| 15 | 3PM | 105 |
>| 16 | 4PM | 129 |
>| 17 | 5PM | 77 |
>| 18 | 6PM | 42 |
>| 19 | 7PM | 5 |
>| 20 | 8PM | 9 |
>| 21 | 9PM | 6 |
>| 22 | 10PM | 7 |
>| 23 | 11PM | 2 |
>+------+------------+-------+
>16 rows in set (4.71 sec)
>
>Is there a way to get the AVG count per day over this range instead of the
>above total (in one query)?
Sir, it's not clear what you want here. To calculate the mean count
per day, you would have to have counts for at least two days, but the
range you're showing is only part of a day. I think what you want is
the mean count per hour over this range. If that is so, you can try
SELECT Count(*)/Count(DISTINCT Hour(closedtime))
>I can't seem to find info on grouping by several things.
If you mean that you want to apply aggregate functions to two levels
simultaneously, the answer is that SQL doesn't allow it. Sometimes
you can trick SQL into doing it, but the result is a query that
doesn't use indices and runs slowly. For example,
SELECT Sum(Hour(closedtime) = 8), Count(*)/Count(DISTINCT Hour(closedtime))
gives you the count during one hour in the first column and the mean
hourly count in the second. (Assuming it works. I haven't tried it.
You may have to use Sum(IF(Hour(closedtime) = 8, 1, 0))) This gives
you statistics from two different levels, but the boolean evaluation
in the first column will probably prevent the use of indices, as will
the use of Hour() in the second.
Bob Hall
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
