This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 22, 2013 3:56 AM by Frank Kulash

# group by

Currently Being Moderated
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
Currently Being Moderated
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

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
Currently Being Moderated
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

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
Currently Being Moderated
<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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Karthick_Arp wrote:
Your group by must be NVL(SALARY, 0)
Or, should it be length(nvl(salary,0)) ?
• ###### 7. Re: group by
Currently Being Moderated
Only SALARY should do.

``````select SUM((AVG(LENGTH(NVL(SAL,0))))) sm
from emp
group by sal;

SM
------
46``````
• ###### 8. Re: group by
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
``````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
Currently Being Moderated
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

#### Legend

• Correct Answers - 10 points