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;
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;