2 dollar 20 cents 2 dollar 20 cents - 1 month ago 6
SQL Question

SQL statement to get the MIN() from the AVG() returned from second query

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.

SELECT userID
FROM myTable
WHERE time = MIN(...)

SELECT userID, AVG(date_time)
FROM myTable
GROUP BY userID


The second query will return me the average between two times and group by a third party.

Then my first query need to find the minimum average time return from my second query. How can I combine both of the queries together?

The sample data for my second query is like:

user1 20
user2 45
user3 10


Then for my first query, I need to get the user with minimum average:

user3 10


Thanks in advance.

Answer

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.