SQL – using alias in Group By

by

SQL is implemented as if a query was executed in the following order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

More Related Contents:

  1. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  2. Select first row in each GROUP BY group?
  3. Using group by on multiple columns
  4. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  5. GROUP BY with MAX(DATE) [duplicate]
  6. Group by minimum value in one field while selecting distinct rows
  7. How to group time by hour or by 10 minutes
  8. Using an Alias in a WHERE clause
  9. Concat groups in SQL Server [duplicate]
  10. Ms Access Query: Concatenating Rows through a query
  11. SQL query for finding records where count > 1
  12. GROUP BY + CASE statement
  13. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  14. SQL GROUP BY CASE statement with aggregate function
  15. Mysql: how to select groups having certain values?
  16. How to write UPDATE SQL with Table alias in SQL Server 2008?
  17. MySQL #1140 – Mixing of GROUP columns
  18. SQL Server Update Group by
  19. Selecting all corresponding fields using MAX and GROUP BY
  20. PostgreSQL does not accept column alias in WHERE clause
  21. How can I group time by hour or by 10 minutes?
  22. Huge performance difference when using GROUP BY vs DISTINCT
  23. sql group by only rows which are in sequence
  24. Using alias in query and using it
  25. SQL not a single-group group function
  26. MySQL GROUP BY behavior
  27. Conditional SQL count
  28. SQL: How do you select only groups that do not contain a certain value?
  29. SQL query to group by day
  30. How to get list of values in GROUP_BY clause?

Leave a Comment