Mysql compare average results with current result in a single query -

i wondering if it's possible run single query tells me following:

get names of applications have (currently executing) jobs exceeding average historical runtime

table 1: job history

• job start time
• job end time
• job status (success, failure, executing, etc.)
• application id triggered job

table 2: applications

• id
• name

it seemed simple question answer, creating mysql query has proven rather involved (albeit intriguing) challenge...

the query needs grab jobs satus = executing, use application.id other jobs have matching application.id , status = success, average (end_time - start_time), , compare average time (current_time - start_time) of executing jobs. finally, must use application id jobs fail test grab application.name application table. possible in single query?

for sake of question, let's assume current_time passed in argument.

i made attempt @ triple nested query, i'm getting following error, , don't know why. i've spent few hours trying work, i'm in on head here:

error 1054 (42s22): unknown column 'jh.start_time' in 'having clause'

this attempt:

select name application application.id in (     select application_id      job_history jh     application_id in (         select application_id          job_history         status='execution' )     , jh.status='success'     having (avg(jh.end_time - jh.start_time)) < (current_time - jh.start_time)     ); 

edit: suggested, here sample data.

table 1

+--------+------------+------------+----------+----------------+ | job_id |   status   | start_time | end_time | application_id | +--------+------------+------------+----------+----------------+ | job1   | successful |        100 |      200 | app1           | | job2   | failed     |        150 |      350 | app2           | | job3   | successful |        200 |      400 | app1           | | job4   | execution  |        500 |        0 | app1           | | job5   | successful |        600 |      800 | app3           | +--------+------------+------------+----------+----------------+ 

table 2

+------+------------------+ |  id  |       name       | +------+------------------+ | app1 | team green's app | | app2 | team blue's app  | | app3 | team red's app   | +------+------------------+ 

i grab job4, use application_id find job1 , job3. take average run time of job1 , job3 , compare against current runtime of job4. if current runtime greater average runtime, report application name: team green's app.

if i'm understanding question correctly, here's 1 option using join. here it's easy adjust where criteria needs:

select distinct a.name applications      join jobhistory jh on a.id = jh.applicationid     join (       select applicationid, avg(endtime - starttime) avgtime       jobhistory       status = 'success'       group applicationid     ) t on a.id = t.applicationid jh.status = 'execution' ,    @current_time - jh.starttime > t.avgtime