Why group by clause use 'month' alias and works? I thought 'select' clause is executed after 'group by'. Am i right that functions are executed before any clauses?
select date_trunc('month', starttime) as month, count(*)
from cd.bookings
group by month
order by month
select is not executed after group by. It is not before either.
A SQL query expresses the result set that the database operations are supposed to produce. In fact, the actual processing is typically a directed acyclic graph (DAG) of dataflow operations -- very far from the clauses in the SQL query itself.
The ANSI standard specifies that the logic ordering of interpreting aliases. In general, the aliases defined in the select are not available for the where and group by clauses, for instance.
However, some databases extend the scoping rules (MySQL is another) and allow the use of such aliases in the group by. This is considered a feature. But it can result in misleading interpretations when a column alias conflicts with the name of a column in a table.
