Re: NULL values in arithmetic

In article <965294019.687690_at_lnd-hst-1.atlas.net.uk>,   "Matt Pryor" <matt_at_ddm-net.com> wrote:
> Great, thanks I'll give that a try.
>
> Still don't understand the logic behind "nothing plus something =
 nothing"
> though!

Think of it this way. Theoretical controversy aside, most people use the NULL value to mean unknown (maybe unknowable, maybe not yet entered, but in some way unknown). In that case, the sum of 10 + NULL is unknowable. Saying it should be 10 means you assume NULL is zero.

Consider this: if I ask you what the total cost of a pound of steak and a loaf of bread is, and you know the cost of the bread but not the steak, what's your answer? If you just say it's the cost of the bread you're saying the steak costs nothing, which is clearly wrong. The correct answer is: I don't know.

This is true if you sum across a row, as in your example. It's different if you sum down a column. Consider the table test:

  X
  -
  1
  2
  3
  NULL The command:
select sum(x), avg(x), count(x), count(*) from test;

gives the results:

    SUM(X) AVG(X) COUNT(X) COUNT(*) ---------- ---------- ---------- ----------

         6 2 3 4

Note the overall count is 4, but the count of x is only for the non-null columns. This is what you want. It says the average value of x is 6, making the assumption that the missing value is the norm, which is standard in statistics. The null value just doesn't enter into the calculations.

--
Jim Lyons                     |  Network Systems Analyst
Entertech - The IC2 Institute |  512-482-0273, ext. 269
University of Texas at Austin |  jlyons_at_weblyons.com
http://www.weblyons.com


Sent via Deja.com http://www.deja.com/
Before you buy.