Systems Engineering and RDBMS

More on the NULL values

Posted by decipherinfosys on April 13, 2007

Another thing to remember about NULL values is that they get ignored in the aggregate functions like Avg().  Hence it is advisable that while modeling those attributes which will be required to be a part of the aggregation – like money related attributes in an accounting or a financial software, quantity related attributes in an inventory system etc.

Here is an example:

declare @demo_agg table (col1 numeric(5,2) null)
insert into @demo_agg values (10 )
insert into @demo_agg values (10 )
insert into @demo_agg values (null )
select avg(col1) as col1
from @demo_agg

col1
———–
10
Warning: Null value is eliminated by an aggregate or other SET operation.

We had inserted three values – 10, 10 and Null…the aggregate function avg() omitted the NULL value and thus calculated (10+10)/2 = 10.  Even though there were  three records.  Now, if you are using NULL to represent no data available, and this is what you desired as the output, it is fine – but in most systems, this requires the third value to be treated as a 0 and thus the output should have been  6.67 which would be the true average since there were three records in the table.

declare @demo_agg table (col1 numeric(5,2) null)
insert into @demo_agg values (10 )
insert into @demo_agg values (10 )
insert into @demo_agg values (0 )
select avg(col1) as col1
from @demo_agg

col1
—————————————
6.666666

Sorry, the comment form is closed at this time.

 
%d bloggers like this: