1 2 Previous Next 18 Replies Latest reply on Jan 22, 2013 11:56 AM by Frank Kulash Go to original post
• ###### 15. Re: group by
Niteshkhush wrote:
Yes you might be using group functions incorrectly .. Just post your SELECT query which you are using and which is throwing errors as i guess problem is you are not making use of nested functions properly so first post your query ..
``````SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
FROM EMPLOYEE``````
• ###### 16. Re: group by
SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
FROM EMPLOYEES
GROUP BY SALARY;

And if you are removing group by clause from this query means it will obviously throw an error because you are nesting group functions SUM and AVG so group by clause is must ...
• ###### 17. Re: group by
You can remove group by clause by removing AVG or SUM group function but then it might not meet your requirements.. Because if you are nesting group functions means group by clause is compulsory to use ..
• ###### 18. Re: group by
Hi,
Rahul India wrote:
When i remove the group by clause it throws an error
``ORA-00978:     nested group function without GROUP BY``
why this error occurs?
Because you have nested aggregate functions (in this case, the aggregate AVG function is nested inside the aggregate SUM function), but you don't have a GROUP BY clause.

If you only have <b>UN-</b>nested aggregate functions, then the GROUP BY clause is optional: the function will work whether you have one of not. If you have a GROUP BY clause, then the function will be applied spearately to each group, and if you don't have a GROUP BY clause, the function will be applied to all rows, as if they were all one group.
If you have nested aggregate functions, then the inner function is applied to each group separately, and then the outer function is applid to the results of the GROUP BY clause, incuding the inner function.

What are you trying to do? Post the results you want from the given sample data, and an explanation of how you get those results, someone will help you find a good way to do it. (Problems that really need nested aggergate functions are rare.)
1 person found this helpful
1 2 Previous Next