My mySQL call is only returning one value to average where it should be returning all

Question

This is my mySQL statement :

    SELECT DATE(x.created_at), AVG(TIMEDIFF(y.created_at, x.created_at)/60/60)
    FROM 
        (SELECT *
            FROM events a 
            WHERE a.created_at > '12-10-10 12:12:12'
                AND a.type = 'Started') x INNER JOIN

        (SELECT *
            FROM events a 
            WHERE a.created_at > '12-10-10 12:12:12'
                AND a.type = 'Complete') y ON x.target_id = y.target_id
    GROUP BY DATE(x.created_at)
    ORDER BY DATE(x.created_at)    

Ideally, I am trying to get a running median of every date. So that would be for each date, select the time differences between the created_at column, and find the median TIMEDIFF between all the values of that date. And so on, and so forth to each date so long as they have a corresponding complete.

As a shorter goal, my above code block is only "averaging" ( since I can't figure out median ), the last returned value of each date, not all of them that fall on that date.

Any hints, tips, answers?

Answer 1

Given data suitable for testing, our query seem to work. Se this corrected SQL Fiddle: http://sqlfiddle.com/#!2/8458d/3/0

However, the query can be simplified to

SELECT DATE(a.created_at),
       AVG(TIMEDIFF(b.created_at, a.created_at)/60/60)
FROM events a
INNER JOIN events b
ON a.target_id = b.target_id
WHERE a.created_at > '2012-10-10 12:12:12'
AND a.type = 'Started'
AND b.created_at > '2012-10-10 12:12:12' -- not needed?
AND b.type = 'Completed'
GROUP BY DATE(a.created_at)
ORDER BY DATE(a.created_at)