1 2 Previous Next 18 Replies Latest reply: Jan 22, 2013 5:56 AM by Frank Kulash RSS

    group by

    Rahul_India
      SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
      FROM EMPLOYEES
      GROUP BY SALARY;
      Assume there are ten employee records and each contains a SALARY value of 100, except for
      one, which has a null value in the SALARY field.


      Can you please explain how the query is evaluated.What if i remove the group by clause.
        • 1. Re: group by
          Purvesh K
          Rahul India wrote:
          SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
          FROM EMPLOYEES
          GROUP BY SALARY;
          Assume there are ten employee records and each contains a SALARY value of 100, except for
          one, which has a null value in the SALARY field.


          Can you please explain how the query is evaluated.
          What result does the Query give you?

          It would group the Salaries, since 9 have salary as 100 and one with NULL, there would be two groups 100 and NULL.

          For NULL, the result would be 1 due to use of NVL and Length (I do wonder the use of Length), else you would have found the result to be NULL, since only COUNT and GROUPING functions consider NULL values.

          Excerpt from Documentation:-
          All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls

          Read for more Information on Aggregate Functions, Here.
          What if i remove the group by clause.
          What stops you from trying? Do you fear of breaking the Database, just due to removal of Group By?
          • 2. Re: group by
            Rahul_India
            Purvesh K wrote:
            Rahul India wrote:
            SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
            FROM EMPLOYEES
            GROUP BY SALARY;
            Assume there are ten employee records and each contains a SALARY value of 100, except for
            one, which has a null value in the SALARY field.


            Can you please explain how the query is evaluated.
            What result does the Query give you?

            It would group the Salaries, since 9 have salary as 100 and one with NULL, there would be two groups 100 and NULL.

            For NULL, the result would be 1 due to use of NVL and Length (I do wonder the use of Length), else you would have found the result to be NULL, since only COUNT and GROUPING functions consider NULL values.

            Excerpt from Documentation:-
            All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls

            Read for more Information on Aggregate Functions, Here.
            What if i remove the group by clause.
            What stops you from trying? Do you fear of breaking the Database, just due to removal of Group By?
            dont have databse access now.Will obviously try it.
            i saw this query in a book.
            • 3. Re: group by
              Karthick_Arp
              <strike>
              Rahul India wrote:
              SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
              FROM EMPLOYEES
              GROUP BY SALARY;
              Assume there are ten employee records and each contains a SALARY value of 100, except for
              one, which has a null value in the SALARY field.


              Can you please explain how the query is evaluated.What if i remove the group by clause.
              Your group by must be NVL(SALARY, 0)
              </strike>

              Edited by: Karthick_Arp on Jan 21, 2013 12:40 AM
              Incorrect comment..
              • 4. Re: group by
                873006
                Hi Rahul,

                As per the query we will get the output of value 3 , as you mention about query u have 10 records in one records is null
                but in your query u have give the NVL condition.

                And about group by sal is like group the records as per your explain about salary will have all 100 .by grouping the 100 we will get the 3 ouput of the query.
                • 5. Re: group by
                  stefan nebesnak
                  dont have databse access now.Will obviously try it.
                  i saw this query in a book.
                  You can use online tool to test your SQL queries:
                  http://sqlfiddle.com/
                  • 6. Re: group by
                    jeneesh
                    Karthick_Arp wrote:
                    Your group by must be NVL(SALARY, 0)
                    Or, should it be length(nvl(salary,0)) ?
                    • 7. Re: group by
                      Purvesh K
                      Only SALARY should do.

                      select SUM((AVG(LENGTH(NVL(SAL,0))))) sm
                        from emp
                      group by sal;
                      
                      SM
                      ------
                      46
                      • 8. Re: group by
                        Purvesh K
                        Rahul India wrote:
                        dont have databse access now.Will obviously try it.
                        i saw this query in a book.
                        You do not always need a Oracle installation to try out simple things; Visit Apex.oracle.com to create a Test Workspace for yourself and try the solutions even without having an actual db at hand.

                        To answer your last question, of results without having a Group By:
                        select SUM((AVG(LENGTH(NVL(SAL,0)))))
                          from emp
                        --group by sal;
                        
                        ORA-00978: nested group function without GROUP BY
                        • 9. Re: group by
                          jeneesh
                          Purvesh K wrote:
                          Only SALARY should do.
                          There is no meaning then , right?

                          You are selecting (first GROUP phase), LENGTH(NVL(salary...)) and GROUPING by SALARY..?
                          • 10. Re: group by
                            Purvesh K
                            jeneesh wrote:
                            There is no meaning then , right?
                            Yes, no meaning. Probably due to use of Length function.
                            A simple query of "Sum of Averages of Salary" would have some meaning rather than "Sum of Average of Lengths of Salary".
                            • 11. Re: group by
                              stefan nebesnak
                              If you are using a group function within another group function (AVG,SUM,..) you need to use a GROUP BY clause or remove the extra level of nesting.

                              And group function in not allowed in a WHERE or GROUP BY clause (except HAVING clause)
                              • 12. Re: group by
                                Frank Kulash
                                Hi,
                                Rahul India wrote:
                                SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
                                FROM EMPLOYEES
                                GROUP BY SALARY;
                                Assume there are ten employee records and each contains a SALARY value of 100, except for
                                one, which has a null value in the SALARY field.
                                Why assume anything? Post a little sample data, or use some commonly available table, such as scott.emp.
                                Can you please explain how the query is evaluated.
                                To undestand a nexted expression like that, start with the most deeply nested expressionn, and procede outwards.
                                <tt>NVL (salary, 0)</tt>     returns 0 if salary is NULL, otherwise salary, that is, it treats NULL salaries as 0.
                                <tt>LENGTH (NVL (salary, 0))</tt>     converts the number returned by NVL into a string, and then finds the length of that string. For example, if salary is 10.25, then the string version of salary is '10.25', and its length is 5. (Notice this is not an indication of how large the number is. 1025, which is 100 times greater than 10.25, only has a length of 4.)
                                <tt>AVG (LENGTH (NVL (salary, 0)))</tt>     is the average length of those strings in each group, as defined by the GROUP BY clause.
                                <tt> (AVG (LENGTH (NVL (salary, 0)))))</tt>     is the same as above. The extra parentheses don't change anything.
                                <tt>SUM ( (AVG (LENGTH (NVL (salary, 0))))))</tt>     is the total of all the numbers produced by the expression above. Since you're nesting one aggregate function inside another (AVG is nested inside SUM in this case), you must have a GROUP BY clause, and the GROUP BY applies only to the inner function (AVG here). The outer function applies to the grouped results, and the result set will contain exactly 1 row, covering all the groups.
                                What if i remove the group by clause.
                                When you get a chance, then try it. If you don't understand the results, ask a specific question, such as "I expected ... because ... but the results I got were ... Why?"
                                • 13. Re: group by
                                  Rahul_India
                                  create table employees(salary number);
                                  
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  
                                  
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  
                                  
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  insert into employees values(100);
                                  commit;
                                  When i remove the group by clause it throws an error
                                  ORA-00978:     nested group function without GROUP BY
                                  why this error occurs?
                                  • 14. Re: group by
                                    Nitesh.
                                    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 ..
                                    1 2 Previous Next