MySQL Forums
Forum List  »  InnoDB

Re: Average of datedifference
Posted by: Lars Röttiger
Date: January 19, 2009 04:11AM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
4210
January 16, 2009 06:17AM
Re: Average of datedifference
3987
January 19, 2009 04:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.