CODEWITHSUNDEEP
sqldatabaseoracle-databaseaggregate-functions
Mushkan Tisekar
Mushkan Tisekar

Reputation: 23

Nested aggregate functions

SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

What's wrong with this query?

Avg returns single value no Max requires a group to work on so it dosent execute and give error? Please explain working It's a quiz question according to which it won't execute I want to know the reason why it dosent execute I can't figure it out nested aggregate functions are allowed right? enter image description here

Upvotes: 1

Views: 602

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Oracle allows nested aggregation functions (see the documentation).

However, it requires a GROUP BY. So this is allowed:

SELECT MAX(AVG(SYSDATE - inv_date))
FROM invoice
GROUP BY Cust_ID;

Basically, this is a short-cut for:

SELECT MAX(x)
FROM (SELECT AVG(SYSDATE - inv_date) as x
      FROM invoice
       GROUP BY Cust_Id
     ) i;

In your case, though, there is no GROUP BY. Oracle doesn't allow nested GROUP BY without the GROUP BY.

And if you are curious, I'm not a fan of this extended functionality. I don't see that it actually solves a problem.

Upvotes: 3

Related Questions