Limitations for directly using column alias for calculation in SQL queries

In a given SQL query, trying to use column aliases avg_time and in the expressions ROUND(avg_time * cnt, 2) will cause the error "Column "avg_time" does not exist".

The root cause is the calculation order of the

SELECT statement. The program processes the entire SELECT statement at the same time, so the alias value cannot be recognized at that point in time.

Solution: Use nested subqueries ]

To solve this problem, subqueries can be used to encapsulate the query, thereby effectively creating an intermediate dataset. In this subquery, you can create the required column aliases

avg_time and cnt.

SELECT stddev_time, max_time, avg_time, min_time, cnt, ROUND(avg_time * cnt, 2) as slowdown
FROM (
    SELECT 
        COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
        MAX(time) as max_time, 
        ROUND(AVG(time), 2) as avg_time, 
        MIN(time) as min_time, 
        COUNT(path) as cnt, 
        path
    FROM 
        loadtime
    GROUP BY
        path
    ORDER BY
        avg_time DESC
    LIMIT 10
) X;

Now, when this query is executed, the subquery is first calculated to generate a dataset containing the desired column alias. Then, the subsequent

SELECT statement can successfully refer to these aliases.