Re: Average of datedifference
I've found the solution. I need a view.
Here the view:
select `p`.`name` AS `Product`,`c`.`name` AS `Component`,`b`.`priority` AS `priority`,`b`.`bug_severity` AS `bug_severity`,count(1) AS `Counter`,(round(unix_timestamp(max(`ba`.`bug_when`)),0) - round(unix_timestamp(`b`.`creation_ts`),0)) AS `DateDiff` from (((`bugs` `b` join `components` `c` on((`c`.`id` = `b`.`component_id`))) join `products` `p` on((`p`.`id` = `b`.`product_id`))) join `bugs_activity` `ba` on(((`ba`.`bug_id` = `b`.`bug_id`) and (`ba`.`added` = _utf8'Fixed')))) where ((1 = 1) and (`b`.`bug_status` = _utf8'CLOSED')) group by `p`.`name`,`c`.`name`,`b`.`priority`,`b`.`bug_severity`,round(unix_timestamp(`b`.`creation_ts`),0)
And now I can select the avg:
select Product, Component, priority as prio, bug_severity as sev, sum(Counter) as 'Sum Bugs',
ROUND(AVG(DateDiff)/3600,2) as AverageHours
from vwBugTimeFixed
GROUP BY priority, bug_severity, Product, Component
ORDER BY priority, bug_severity, Product, Component;