Avoiding Sub Queries when getting average-CodePudding

I wanted to get the average of how long does people stays, I know this query is n to optimized and wanted to fix it. Are there a way to avoid sub query when doing something like this for example :

WITH avg_stay AS (
        SELECT AVG(Duration) AS avg_duration
        FROM (
            SELECT Stayend - Staystart AS Duration
            FROM Stay
        ) AS Dur
),

I think from select can be avoid in this situation

CodePudding user response：

You should be able to just take the average in a single pass over your table:

SELECT AVG(Stayend - Staystart) AS avg_duration
FROM Stay;

So, you do not need the subquery here.

CodePudding user response：

you can simplify your query to:

SELECT AVG(Stayend - Staystart) AS avg_duration
FROM Stay;