T-SQL - Aggregate functions and NULL values in numeric fields

Except COUNT(*), all other aggregate functions ignore NULLs. As an example if you have NULL value in a numeric field, the NULL value does not equal to zero. Look at the following code, it shows the AVG function ignores the records which their specific column has NULL value.

CREATE TABLE theTable
(
theId int IDENTITY NOT NULL,
theValue DECIMAL(10,2) NULL
)

INSERT INTO theTable
(theValue)
VALUES
(NULL), (0), (1), (2), (3), (NULL)

SELECT AVG(theValue) as AverageValue
FROM theTable

The result is 1.500000

It's important when you want to design your database and tables, because it will affect the queries result when you run them against the database.

On the other hand, it's possible to consider COALESCE function (http://msdn.microsoft.com/en-us/library/ms190349.aspx), it gives you a chance to select first non NULL expression among its arguments. Then you can send multiple field names and let the COALESCE find the first non NULL value for your aggregate function, it can be happen if you have alternative field but it's tricky and depends on business rules in your application.

T-SQL - Aggregate functions and NULL values in numeric fields