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

Source: Internet
Author: User

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

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.