Why do aggregation functions work without Group By?

Question

Consider a table called Employee with only the columns Employee_ID and Salary. Clearly

Select Max(Salary) from Employee;

works and returns exactly one row.

But why does this work? My points of confusion are as follows.

1. Max clearly isn't a window function; It's an aggregation function.
2. This can't be like Pivot, where unstated columns are put inside an implicit Group By, because Select Max(Salary) From Employee Group By Employee_ID would return one row per Employee_ID.
3. Every bit of documentation that I can find about aggregation functions has an explicit Group By.

Answer 1

The query SELECT MAX(Salary) FROM Employee works without an explicit GROUP BY clause because it is an example of an aggregate query without any grouping specified. In such cases, the aggregate function operates on the entire result set, treating it as a single group.

You are correct that MAX is an aggregation function, not a window function that performs calculations on a set of rows and returns a single value as the result.

Unlike the PIVOT operation, which involves transforming rows into columns based on specific grouping criteria, the query SELECT MAX(Salary) FROM Employee does not involve any grouping. It simply calculates the maximum value of the Salary column across all rows in the table.

According to Itzik Ben-Gan in his book T-SQL fundamentals (a book that I really enjoyed), when using aggregation functions, you would need to include a GROUP BY clause to specify the grouping criteria. However, if you omit the GROUP BY clause, the aggregation function operates on the entire result set as a single group. This behavior is specified by the SQL standard and allows you to perform aggregate calculations without explicitly grouping the data.

In the case of the query SELECT MAX(Salary) FROM Employee, it calculates the maximum salary value across all employees and returns a single row with that value.