This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 22, 2013 3:56 AM by Frank Kulash Go to original post RSS
  • 15. Re: group by
    Rahul_India Journeyer
    Currently Being Moderated
    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
    Nitesh. Explorer
    Currently Being Moderated
    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
    Nitesh. Explorer
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points