CODEWITHSUNDEEP
sqloracle-database
user234568
user234568

Reputation: 815

Query dubious understanding

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....

 SELECT SUM((AVG(LENGTH(NVL(SALARY,0)))))
 FROM DUCK
 GROUP BY SALARY;

So what does the last bracket do when the sum function computes a list of data. but the avg function computes out the data which leaves to a digit?

Upvotes: 1

Views: 314

Answers (2)

Popeye
Popeye

Reputation: 35900

Your query is running like this:

  • null is considered as 0
  • length of the salary is calculated. For 100, it is 3 and for null salary (0), it is 1 (length of 0 is 1)
  • average of the salaries group by salary amount. So there will be two groups, 1st is 100 and 2nd is 0. The average will be 3 for the first group and 1 for the 2nd group.
  • the sum of all averages. That is 3+1 = 4

So for sample data mentioned in your case, it will be 4.

See this db<>fiddle to get an idea.

Upvotes: 1

Fact
Fact

Reputation: 2420

In my view the group by clause at the end is what's to be noted. Without the group by you are Not able to do a nested group by. Trying to do it results in 

With sal as
(
Select 100 salary from dual union all
Select 200 from dual union all
Select 300 from dual union all
Select 10 from dual union all
Select 20 from dual union all
Select 40 from dual union all
Select 50  from dual union all
Select 50  from dual union all
Select null from dual union all
Select null from dual
)
Select SUM(AVG(LENGTH(coalesce(SALARY,0))))
--,AVG(LENGTH(coalesce(SALARY,0)))
from sal

ORA-00978: nested group function without GROUP BY
00978. 00000 -  "nested group function without GROUP BY"
*Cause:    
*Action:

Now when you add the group by , the sum is repeated for each grouped by salary. So in this case you are summing the value 2.1 9 times . The value 2.1 is derived by the AVG AVG(LENGTH(coalesce(SALARY,0))). NULL are counted as invididual records and not as one after being grouped hence 9 and not 8 records.

Hope this helps. By the way what does it do in your application, the use case? just curious.

Upvotes: 1

Related Questions