Why can’t I use column aliases in the next SELECT expression?

by

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;

More Related Contents:

  1. Using an Alias column in the where clause in Postgresql
  2. PostgreSQL: using a calculated column in the same query
  3. Select first row in each GROUP BY group?
  4. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  5. How to use RETURNING with ON CONFLICT in PostgreSQL?
  6. Does PostgreSQL support “accent insensitive” collations?
  7. PostgreSQL DISTINCT ON with different ORDER BY
  8. PostgreSQL: running count of rows for a query ‘by minute’
  9. Split comma separated column data into additional columns
  10. IN vs ANY operator in PostgreSQL
  11. How to delete duplicate entries?
  12. Transpose latest rows per user to columns
  13. Delete duplicate rows (don’t delete all duplicate)
  14. subquery in FROM must have an alias
  15. Compute percents from SUM() in the same SELECT sql query
  16. SQL Between clause with strings columns
  17. Insert an image in postgresql database
  18. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  19. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  20. Error: Column does not exist
  21. Postgresql Column Not Found, But Shows in Describe
  22. DROP FUNCTION without knowing the number/type of parameters?
  23. INSERT COMMAND :: ERROR: column “value” does not exist
  24. Return multiple columns and rows from a function PostgreSQL instead of record
  25. Postgres error updating column data
  26. Check whether string is a date Postgresql
  27. SQL LIKE condition to check for integer?
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. How to bulk insert only new rows in PostreSQL
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment