The usage of NULL with function

You can see the usage of NULL with function by the following scripts.
FUNCTION:
AVG()、
COUNT()、
SUM()

AVG():
It doesn't consider the column which is null.
If the record has 5 rows,the specified column has 3 real values and 2 null values,the AVG equals: the summmary of the 3 real value divied 3.(not 5)
COUNT():
It doesn't consider the column which is null.
If the record has 5 rows,the specified column has 3 real values and 2 null values,the count equals:3(not 5)
SUM():
It doesn't consider the column which is null.
If the record has 5 rows,the specified column has 3 real values and 2 null values,the sum equals: the summary of the 3 real values.
NOTE:
We usually use SUM(NVL(the specified column,0)), this method is not necessary,we can directed use SUM(the specified column)

[@more@]

SQL> CREATE TABLE test_0407
2 (
3 id VARCHAR(2),
4 value_1 NUMBER,
5 value_2 NUMBER
6 );

表已创建。

SQL> INSERT INTO test_0407
2 VALUES('1',10,10);

已创建 1 行。

SQL> INSERT INTO test_0407
2 VALUES('2',10,10);

已创建 1 行。

SQL> INSERT INTO test_0407
2 VALUES('3',NULL,10);

已创建 1 行。

SQL> INSERT INTO test_0407
2 VALUES('4',NULL,10);

已创建 1 行。

SQL> INSERT INTO test_0407
2 VALUES('5',20,10);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT AVG(value_1),AVG(value_2)
2 FROM test_0407;

AVG(VALUE_1) AVG(VALUE_2)
------------ ------------
13.3333333 10

SQL> SELECT SUM(value_1),SUM(value_2)
2 FROM test_0407;

SUM(VALUE_1) SUM(VALUE_2)
------------ ------------
40 50

SQL> SELECT COUNT(*),COUNT(value_1),COUNT(value_2)
2 FROM test_0407;

COUNT(*) COUNT(VALUE_1) COUNT(VALUE_2)
---------- -------------- --------------
5 3 5

SQL> commit;

提交完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10484922/viewspace-1001976/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10484922/viewspace-1001976/