Adding a null makes the sum null

[BroncoMarc]

I have a query that adds 12 months of forecast data, but some of the data contains nulls. The problem is that if any one of the 12 months contains a null it makes the result null. Is there an easy way to make it look at the nulls as zeros?

The data is on a SQL server, so I have no control of the source data.


- Marc

 

[Mile-O]

Use the Nz() (Null to Zero) function.

 

[dcx693]

Mile, is it really called the "Null To Zero" function? I just thought they used Nz() because it would most commonly be used to substitute 0 for Null. Didn't know if it was the official designation.

 

[Mile-O]

No idea!

 

[Pat Hartman]

Yes, Null to Zero is what it is called. If your table were properly structured, you wouldn't have a problem with null values since the aggregate and domain aggregate functions handle nulls properly (they ignore them). You only have this trouble when you are doing your own calculations as in - (fld1 + fld2 + fld3)/3

 

[Fizzio]

Pat, are nulls not unavoidable in all tables?

 

[Pat Hartman]

Try not to use double negatives when posing questions. It makes them hard to interpret

Yes you can avoid nulls if you desire by making all columns required and assigning default values.

This is not always desirable however since there is a logical difference between an unknown value and one that is the default. Take something like AnnualRaise. A 0 value should indicate that the annual raise is known and it is zero whereas a null value would indicate that the raise hasn't yet been set.

Nulls are quite valuable and should not be avoided since they convey information that is not available in any other way.

It is important however, to learn how to use them properly. For example if you wrote a query that selected all employee records with AnnualRaise Not > 0, you might expect to get records with 0 or null as the AnnualRaise amount but you would get ONLY records with 0 values. I have written numerous posts regarding the care and handling of nulls. Search for them for more info. You can also read the help entry.

 

[Fizzio]

Thanks Pat. I shall not unavoid using double negatives in the future