Warning: Null value is eliminated by an aggregate or other SET operation.

Whenever you are using Aggregate function, keep a special cleansing activity for NULL value before you apply aggregate function on it.

By default SQL Server escape null value while calculating aggregate. Let’s see with an example.

Here we are creating a table to hold some null value in salary column.

create table testNullAggregate_TB

(id int identity(1,1),

dept_id int,

ename varchar(10),

salary int)

Command(s) completed successfully.

Lets insert some records with null values in Salary field

insert into testNullAggregate_TB values(1,'Suprabha',2000)

insert into testNullAggregate_TB values(2,'Lopamudra',null)

insert into testNullAggregate_TB values(1,'Sourabhi',2000)

insert into testNullAggregate_TB values(1,'Akansha',null)

insert into testNullAggregate_TB values(2,'Pragya',2000)

insert into testNullAggregate_TB values(1,'Sulagna',2000)

insert into testNullAggregate_TB values(3,'Priya',2000)

Testing Aggregating Function with Null Values

Here we are checking total row count on the basis of salary field.

select count(salary) from testNullAggregate_TB

RowsCount

-----------

5

Here we saw actual Number of records present in table is 7 but count(salary) escape the record count where it found null in salary equal null.

Here we are trying to find the number of employee in each department.

select dept_id, count(salary) as 'No of Employee' from testNullAggregate_TB

group by dept_id

dept_id     No of Employee

----------- --------------

1           3

2           1

3           1

Here again we saw, it produce wrong result. Dept 1 has 4 employee whereas result shows 3, similarly Department 2 has 2 employee whereas Result shows 1.

Here we are trying to find average salary for each department.

select dept_id, avg(salary) as 'Avg Salary' from testNullAggregate_TB

group by dept_id

dept_id     Avg Salary

----------- -----------

1           2000

2           2000

3           2000

Here we saw the average salary for department 1 and 2 are not correct.

How to Rectify the Problem occurred while using Aggregate function with null values.

ISNULL function we will be using to rectify the null problems.

Total Number of Rows on the basis of salary

select count(isnull(salary,0)) as RowsCount from testNullAggregate_TB

RowsCount

-----------

7

Total employee for each department.

select dept_id, count(isnull(salary,0)) as 'No of Employee' from testNullAggregate_TB

group by dept_id

dept_id     No of Employee

----------- --------------

1           4

2           2

3           1

Average salary for each department.

select dept_id, avg(isnull(salary,0)) as 'Avg Salary' from testNullAggregate_TB

group by dept_id

dept_id     Avg Salary

----------- -----------

1           1500

2           1000

3           2000

Let  your code speak, you be silent!!!

Post Reference: Vikram Aristocratic Elfin Share