sql - Why can't I use column aliases in the next SELECT expression?

Question

Can I modify the next to use the column aliases avg_time and cnt in an expression ROUND(avg_time * cnt, 2)?

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, 
    ROUND(avg_time * cnt, 2) as slowdown, path
FROM 
    loadtime
GROUP BY
    path
ORDER BY
    avg_time DESC
LIMIT 10;

It raises the next error:

ERROR:  column "avg_time" does not exist
LINE 7:  ROUND(avg_time * cnt, 2) as slowdown, path

The next, however, works fine (use primary expressions instead of column aliases:

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, 
    ROUND(AVG(time) * COUNT(path), 2) as slowdown, path
FROM 
    loadtime
GROUP BY
    path
ORDER BY
    avg_time DESC
LIMIT 10;

Answer 1

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

The solution is to encapsulate the query in a subquery and then the alias is available outside.

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;