Execution order of aggregate function in sql

Question

Order of execution of query in sql is follow according to my knowledge.

1. FROM
2. ON
3. OUTER/Inner
4. WHERE
5. GROUP BY
6. CUBE or ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Can anybody tell me order of execution of aggregate function.In my select clause I have 2 aggregate function.Which one get executed first?

Select City, Gender, SUM(Salary) as TotalSalary, 
COUNT(ID) as TotalEmployees
from tblEmployee
group by City, Gender

Possible duplicate of [Order Of Execution of the query](http://stackoverflow.com/questions/4596467/order-of-execution-of-the-query) — MusicLovingIndianGirl, Nov 04 '15 at 12:17
Your first statement is simply incorrect. There is no order of execution in queries, or at least none that is so easily defined. — Gordon Linoff, Nov 04 '15 at 12:21
Logical execution order, perhaps. But the dbms may execute it in a completely different order. — jarlh, Nov 04 '15 at 13:34

Lukasz Szozda · Answer 1 · 2015-11-04T12:25:54.427

1

They are executed all-at-once

"All-at-Once Operations" means that all expressions in the same logical query process phase are evaluated logically at the same time.

Your SUM and COUNT are in the same process phase.

Keep in mind that all-at-once mean also you cannot do something like:

SELECT 1 AS col1,
       col1 + 1 AS col2;

You will get: Invalid column name 'col1'.

edited Nov 04 '15 at 12:25

answered Nov 04 '15 at 12:19

Lukasz Szozda

162,964
23
234
275

1

As of August 2018 the query you posted is valid thanks to introduction of _lateral column alias references_ - https://aws.amazon.com/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/ – botchniaque Dec 12 '19 at 23:35
@botchniaque That is the same situation like in MS Access. It is a Redshift extension(syntactic sugar) rather than SQL Standard I believe. Based on doc it is inlining: `"The benefit of the lateral alias reference is you don't need to repeat the aliased expression when building more complex expressions in the same target list. When Amazon Redshift parses this type of reference, it just inlines the previously defined aliases."` The SQL Standard way is for instance [`LATERAL JOIN`](https://stackoverflow.com/a/36530228/5070879) - but not as expressive as lateral column alias – Lukasz Szozda Dec 13 '19 at 13:41
@botchniaque Btw. could you check sth for me? If it is really inlining then undeterministic funcion should return different results: `SELECT RAND() AS c, c+1 AS d` => `SELECT RAND() AS c, RAND() + 1 AS d` - I am really curious about the resultset – Lukasz Szozda Dec 13 '19 at 13:46
1

Both above functions return same results - the alias expression is re-evaluated: `SELECT RANDOM() AS c, c+1 AS d;` => example result `0.3923622576484281,1.7947717210253569` – botchniaque Dec 13 '19 at 14:09
@botchniaque I am just saying that this is couter intuitive then for undeterministic function. `SELECT RAND() AS c, c+1 AS d` => I would expect `0.39 and 1.39` Instead inlining causes 2 seperate runs. – Lukasz Szozda Dec 14 '19 at 10:44
@botchniaque https://stackoverflow.com/questions/59334543/amazon-redshift-lateral-column-alias-reference Interesting topic – Lukasz Szozda Dec 14 '19 at 11:28

score 0 · Answer 2 · answered Nov 04 '15 at 12:20

Your list looks a lot like SQL Server's description of the logical processing order of queries (which is here).

This actually has nothing to do with the actual execution order. It describes how the clauses are interpreted when the SQL query is being complied. In particular, it explains why a column alias defined in the SELECT is not available in the WHERE clause. The reason is simple: the WHERE is processed before the SELECT, so the alias is not yet defined.

This has nothing to do with the actual execution of the query.

Execution order of aggregate function in sql

2 Answers2

Linked