CODEWITHSUNDEEP
sqlselectgroup-bycase-when
Oleole
Oleole

Reputation: 411

SQL: Using Alias from SELECT in GROUP BY

I am confused about the order of query process. I have thought the order run like as follows:

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

But then, I was able to use a variable alias name created in the SELECT in GROUP BY. I wonder if there's an exception with using CASE WHEN but not sure.

Here is the SQL query and result

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

What you are referring to are the scoping rules of SQL, not the execution order. These define how column and table identifiers are disambiguated in a query. The general ordering is:

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY

In general, the area of interest are column aliases, because table aliases are only defined in the FROM clause.

Because only the SELECT defines new column aliases (apart from those in the tables referenced in the FROM clause), this means that none of the clauses above it in the list can use "new" aliases.

However, some database vendors have found that this is inconvenient. In particular, these databases "extend" the standard by allowing column aliases in the GROUP BY and or HAVING clauses. Some databases have further restrictions, so aliases are allowed, but not expressions containing aliases.

In any case, you are using a database such as Postgres, MySQL or BigQuery that allows column aliases in the GROUP BY.

Upvotes: 1

Related Questions