CODEWITHSUNDEEP
sqlsql-servert-sqlselect
veljasije
veljasije

Reputation: 7092

Nested aggregate function in SELECT list

I tried following query:

SELECT 
    MAX(SUM(e.Empid))
FROM HR.Employees

and got following error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

My question is why isn't this allowed?

Upvotes: 0

Views: 14570

Answers (6)

Raj
Raj

Reputation: 10853

OK. I got your question now. Here is why:

The value expression simply contained in set function specification shall not contain a set function specification or a subquery. If the value expression contains a column reference that is an outer reference, then that outer reference shall be the only column reference contained in the value expression.

Further reading : SQL 92 Standards

Raj

Upvotes: 0

John Woo
John Woo

Reputation: 263803

since you have not define any columns to be grouped, The value of SUM() is equal to MAX()

UPDATE

An error was thrown because MAX(SUM(e.Empid)) requires the results of two grouped selects, not just one.

Upvotes: 1

Andomar
Andomar

Reputation: 238176

Each aggregate works on a group. You can only define one group per query. So multiple aggregates require subqueries. For example, to find the amount of employees in the largest department:

SELECT  MAX(EmpCount)
FROM    (
        SELECT  COUNT(*) as EmpCount
        FROM    HR.Employees
        GROUP BY
                e.Department
        ) as SubQueryAlias

Upvotes: 6

vikas
vikas

Reputation: 2830

Try this

SELECT MAX(_ID)
FROM (SELECT SUM(e.Empid) _ID FROM HR.Employees e) t

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

This query makes no sense, as even if it worked, it would return only one value : the sum of Empid. The MAX function applied on one value is not really useful.

Upvotes: 0

paul
paul

Reputation: 22001

SUM(x) evaluates to a single value, so it's not appropriate to MAX the result of it.

Upvotes: 0

Related Questions