Nested aggregate functions

Question

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

Gordon Linoff · Accepted Answer · 2020-07-17 17:53:53Z

3

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.

edited Jul 17, 2020 at 17:53

answered Jul 16, 2020 at 18:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mushkan Tisekar Over a year ago

in the quiz they said this won't execute or throw error . I want to know the reason why it throws error. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

Gordon Linoff Over a year ago

@MushkanTisekar . . . I elaborated. I hadn't tried a nested GROUP BY without a GROUP BY. Seems a bit odd to me that it doesn't work, but it generates a syntax error without the GROUP BY.

Mushkan Tisekar Over a year ago

By the way could u tell me about the "FROM(.....) i " can we use a variable like this ? U have used in your explanation query . I am a beginner in SQL so I don't know many things.

Gordon Linoff Over a year ago

@MushkanTisekar . . . The is properly called a table alias.

Collectives™ on Stack Overflow

Nested aggregate functions

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related