I got a question regarding subquery in SQL statement. What I am trying to do is to find a minimum time with the average column result returned from another query.
WHERE time = MIN(...)
SELECT userID, AVG(date_time)
GROUP BY userID
If you want one row with the minimum average time, then you can do:
SELECT userID FROM myTable GROUP BY userID ORDER BY AVG(date_time) ASC LIMIT 1;
If you want multiple rows, then you would need a subquery or
join. I think the following will work:
SELECT userID FROM myTable GROUP BY userID HAVING AVG(date_time) = (SELECT AVG(date_time) FROM myTable GROUP BY userID ORDER BY AVG(date_time) ASC LIMIT 1 );
I am always wary of comparing two averages after a calculation, because small differences can arise in the results (even based on the order of the calculation). I think it will work correctly in this case.