Use the nvl function in Oracle Aggregate functions (sum, AVG, etc.) to force null values to be added.

Source: Internet
Author: User

We are introducingOracleIf there is a null value in the given value,OracleThese null values are ignored. This processing method most of the time meets our requirements, but sometimes there may be problems.

For example, when the year ends, the company will reward some employees, but some of them will not be rewarded. For example, if an employee's reward amount is saved in a table, the reward amount corresponding to some employees is null because they are not rewarded at all, now, the boss asks you to calculate the average prize for the entire company.

If you use

Select AVG (bonus) from EMP;

The result is the average reward of those who receive the reward.

What the boss asks is to calculate the average reward amount of all employees in the company.

This is what you should useNvlFunction to forcibly add employees who have not received the rewards.

For example:

Select AVG (nvl (bonus, 0) from EMP;

In ourScottOfSchemaThere is a table inEMP,Although there is no bonus in it, there is anotherColumn Comm .,We can driveCommTo show the prize and test it.

Select AVG (Comm) from EMP;Get

AVG (Comm)

----

550

Select AVG (nvl (Comm, 0) from EMP;Get

AVG (nvl (Comm, 0 ))

------

157.142857

In this way, the statement just now is verified.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.