James Buchert James Buchert -4 years ago 124
MySQL Question

Calculate average of entries in same table mysql

I have a bunch of entries in a table that look like the following

|id | agency_id|old_status|new_statusts|timestamp |contact_id|alter_type |last_mod_by|
|968 |4926185635| Verified| Verified |2016-11-2210:46:56| 969|LeadAdded | 1|
|4274|4926185635| Verified| Verified |2017-01-1410:46:56| 969|NoteAdded | 1|
|4275|4926185635| Verified| Quoted |2017-01-2110:46:56| 969|StatusChange| 1|

What I am trying to do is get a cumulative average of time it takes to from the time it was added to the time it was Verified as well as verified to quoted.

I have looked around through different entries on this site and can't seem to find something that fits.

Effectively what is happening is an entry gets put in when a lead is added (row id 968) then another is added when the lead status is changed from added to Verified (row id 4275) there are tons of these with the unique values being contact_id and agency_id

I want to get the average of all of the entries from Lead Added to Verified

Any help is much appreciated

Answer Source

If I understand correctly, you want the difference between the leadAdded and the statusChange to "Quoted". This suggests conditional aggregation or a join:

select avg(datediff(tq.ts, tv.ts)) as avg_time_to_quote
from t tv join
     t tq
     on tv.agency_id = tq.agency_id and
        tv.alter_type = 'LeadAdded' and
        tq.alter_type = 'StatusChange' and
        tq.new_status = 'Quoted';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download