Solution for null statistical results such as sum () and AVG () in SQL

When the count () function is used in SQL statements, the returned results must be non-negative integers. This is also a very effective method for judging whether data exists;

 

Then, when sum () is used to add a set, if the sum () object does not have a record, 0 is not returned, and null is returned. In this way, the database is not affected, because numeric fields can also store null values, when such SQL statements are executed, no records are recorded and no error is reported.

However, after the execution, this data will be used.

1.ProgramDuring processing, for example, when performing a four-Rule operation or other forced type conversion, it will provide an incorrect type or the result of adding it to other data will become null, because null + number = NULL.

2. There are other operations in the SQL statement of the statistical result. In the last case of 1, null + number = NULL. It will erase other valid data.

Solution:

1. Use the isnull () method for determination, such as isnull (sum (CNT), 0), where 0 is the default value. If sum (CNT) is null, 0 is assigned; similarly, this method can be used for a field, isnull (CNT, 0), so that some numeric fields with null values can be excluded. In this case, if isnull (sum (isnull (CNT, 0) and 0) are used, it is safe.

2. Use the case when else flow control statement. For example, replace sum (CNT) with "case when sum (CNT) is null then 0 else sum (CNT) End ), 0 has the same meaning as the previous one.

In addition, the average function AVG () is the same, and the same solution

 

Reprinted from: http://www.cnblogs.com/pinxu/archive/2010/10/13/1850480.html