[snip] I have a database of tickets and I want to get "the average numbers of tickets created per agent per hour over a date range" in the format:
Hour | Avg per agent This is what I have so far: SELECT HOUR(created) AS hourcreated, COUNT(*) AS sum-per-hour FROM remedy WHERE (created>='2002-4-25' AND created<='2002-04-30') GROUP BY HOUR(created) This query only gives the SUM of tickets per hour. Is there a way to get the number of distinct "submitters" per hour so I can divide that by count to get avg/hour? [/snip] You probably need a crosstab query and I would need to see some of the table to help you create it. It would look something like; SELECT DATE(created) AS DateCreated, SUM(IF(HOUR(created) = '10:00', 1, 0)) as "10 am", SUM(IF(HOUR(created) = '11:00', 1, 0)) as "11 am", SUM(IF(HOUR(created) = '12:00', 1, 0)) as "12 am", SUM(IF(HOUR(created) = '13:00', 1, 0)) as "1 pm" FROM remedy WHERE (created>='2002-4-25' AND created<='2002-04-30') GROUP BY DATE(created) would give something like; +-------------+---------------+----------+-------------+-----------+ | DateCreated | 10 am | 11 am | 12 am | 1 pm | +-------------+---------------+----------+-------------+-----------+ | 2002-04-25 | 159 | 72 | 0 | 0 | | 2002-04-26 | 28 | 0 | 14 | 14 | | 2002-04-27 | 22 | 17 | 17 | 17 | | 2002-04-28 | 36 | 13 | 49 | 85 | | 2002-04-29 | 12 | 0 | 12 | 12 | | 2002-04-29 | 12 | 83 | 72 | 12 | +-------------+---------------+----------+-------------+-----------+ HTH! Jay --------------------------------------------------------------------- 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
