hey,
i ma creating a join of the same table the output i want is something like this:
|Ticket Ref Number|Ticked Opened On|Ticket Closed On|Time Spent In Minutes|TotalAverage|
|0000712 |2011-09-14 |2011-10-18 |20023 | 12hours|
|0000200 |2011-06-14 |2011-10-08 |40023 | (null)|
|0000001 |2010-06-14 |2011-10-02 |20023 | (null)|
|0000099 |2010-07-13 |2011-10-01 |10022 | (null)|
what i get is this :
|Ticket Ref Number|Ticked Opened On|Ticket Closed On|Time Spent In Minutes|TotalAverage|
|0000712 |2011-09-14 |2011-10-18 |20023 | (null)|
|0000200 |2011-06-14 |2011-10-08 |40023 | (null)|
|0000001 |2010-06-14 |2011-10-02 |20023 | 12hours|
|0000099 |2010-07-13 |2011-10-01 |10022 | (null)|
the problem with my output is that the total average time is presented where the ticket ref id is the lowest but what i need is just simply be presented in the top or in every field.
this is the quey i have in order to obtain these results
select
inc.field_11413 As TicketRefNumber,
inc.field_11425 As 'Ticket Opened on',
inc.field_11426 As 'Ticket Closed on',
TIMESTAMPDIFF(MINUTE, inc.field_11425,inc.field_11426) As 'Time Spent in Minutes',
avr.avrage
from
tbl_bpm_data_objects_11411 as inc
Left Join
(
select
field_11426,
AVG(TIMESTAMPDIFF(MINUTE, field_11425,field_11426))/60 as avrage
from tbl_bpm_data_objects_11411
) as avr
on avr.field_11426 = inc.field_11426
order by inc.field_11426 DESC
any help will be appreciated