Board index » delphi » null values in Sum() queries

null values in Sum() queries

Hi,

select sum(A) - sum(B) from Table1

If all the B values are null, the result will always be Null ??!!??
Why ? It should be the first factor result: sum(A)
A and B are Numbers of Paradox. I use D3.02.

Pedro MG
----------------------------------------------------------------------------
-------
TQuadrado - Empresa de Servi?os Informticos, Lda.
R. da Constitui??o, 47, 3o
4200-197 Porto
PORTUGAL
Tel: +351.2.5503992 / 8
Fax: +351.2.5503979
t...@esoterica.pt
t...@esoterica.pt
----------------------------------------------------------------------------
-------

 

Re:null values in Sum() queries


If you get Null values in operations the result will always be Null. If you
don't want this to happen,

write:

"SELECT SUM(A) As SumA, SUM(B) As SumB FROM Table1"

And do your operations in your code...

Quote
"TQuadrado, Lda." wrote:
> Hi,

> select sum(A) - sum(B) from Table1

> If all the B values are null, the result will always be Null ??!!??
> Why ? It should be the first factor result: sum(A)
> A and B are Numbers of Paradox. I use D3.02.

> Pedro MG
> ----------------------------------------------------------------------------
> -------
> TQuadrado - Empresa de Servi?os Informticos, Lda.
> R. da Constitui??o, 47, 3o
> 4200-197 Porto
> PORTUGAL
> Tel: +351.2.5503992 / 8
> Fax: +351.2.5503979
> t...@esoterica.pt
> t...@esoterica.pt
> ----------------------------------------------------------------------------
> -------

Re:null values in Sum() queries


...as i did in fact.
thanx

Pedro MG

--
----------------------------------------------------------------------------
-------
TQuadrado - Empresa de Servi?os Informticos, Lda.
R. da Constitui??o, 47, 3o
4200-197 Porto
PORTUGAL
Tel: +351.2.5503992 / 8
Fax: +351.2.5503979
t...@esoterica.pt
t...@esoterica.pt
----------------------------------------------------------------------------
-------

Quote
Hakan Demirbilek wrote in message <37665A3C.BCE4B...@superonline.com>...
>If you get Null values in operations the result will always be Null. If you
>don't want this to happen,

>write:

>"SELECT SUM(A) As SumA, SUM(B) As SumB FROM Table1"

>And do your operations in your code...

>"TQuadrado, Lda." wrote:

>> Hi,

>> select sum(A) - sum(B) from Table1

>> If all the B values are null, the result will always be Null ??!!??
>> Why ? It should be the first factor result: sum(A)
>> A and B are Numbers of Paradox. I use D3.02.

>> Pedro MG
>> -------------------------------------------------------------------------
---
>> -------
>> TQuadrado - Empresa de Servi?os Informticos, Lda.
>> R. da Constitui??o, 47, 3o
>> 4200-197 Porto
>> PORTUGAL
>> Tel: +351.2.5503992 / 8
>> Fax: +351.2.5503979
>> t...@esoterica.pt
>> t...@esoterica.pt
>> -------------------------------------------------------------------------
---
>> -------

Re:null values in Sum() queries


No, null and zero are not the same. Null means undefined. Something plus
undefined is undefined. Something minus undefined is undefined. This is
correct behavior according to the SQL standard.

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to email questions. To contact me
 for any other reason remove nospam from my address.)

Re:null values in Sum() queries


Quote
>No, null and zero are not the same. Null means undefined. Something plus
>undefined is undefined. Something minus undefined is undefined. This is
>correct behavior according to the SQL standard.

>Bill

yes i know. But in the case of Number field types, and when using aggregate
functions like Sum(), should not SQL standars assume null as zero ? I tried,
but i can't get an explanation for it.

regards,

Pedro MG

Re:null values in Sum() queries


On Wed, 16 Jun 1999 19:29:06 +0100, "TQuadrado, Lda." <t...@esoterica.pt>
wrote:

Quote
>yes i know. But in the case of Number field types, and when using aggregate
>functions like Sum(), should not SQL standars assume null as zero ? I tried,
>but i can't get an explanation for it.

This is the way SQL works, though some SQL implementations *may* handle
this differently if they deviate from the standard.

Zero is a number. It is less than one and greater than negative one. A
value of zero in a table column indicates that some value has been entered
into the column. That is, the column has a valid value.

A NULL value, OTOH, is the lack of a value. Either a value was never
entered or whatever previous value was there has been completely cleared.
NULL values cannot be accurately compared to non-NULL values. A NULL value
is neither greater than zero nor less than. It just does not exist. Because
of this condition relative to non-NULL values (including zero), it cannot
be added to a non-NULL value. (It can, but the result is NULL.)

You might think of a zero value as clear air and a NULL value as a vacuum.

One approach you might take is to ensure that non of your numeric columns
ever contains a NULL. As new rows are added, programmatically ensure they
are given a default value of zero. Existing rows should be modified to
change the NULL values to zero to accommodate your desired calculations.

Another approach, one that assumes you need those NULL values and they
cannot be changed to zeros, is to use multiple SELECT queries concatenated
into one with the UNION join. Since you only have the two columns, the
possible combinations of NULL and not NULL are small:

1. A is NULL and B is not.
2. B is NULL and A is not.
3. Both columns are NULL.

Each SELECT statement would deal with one of these combinations. Where one
of the columns is NULL, a literal zero is substituted in the calculation.

  SELECT 0 - SUM(T1.B) AS Calc
  FROM Table1 T1
  WHERE (T1.A IS NULL) AND (T1.B IS NOT NULL)
  UNION ALL
  SELECT SUM(T2.A) - 0
  FROM Table1 T2
  WHERE (T2.A IS NOT NULL) AND (T2.B IS NULL)
  UNION ALL
  SELECT 0 - 0
  FROM Table1 T3
  WHERE (T3.A IS NULL) AND (T3.B IS NULL)

You could then get the numbers for the Calc column in three records this
produces as a sungle number by saving the above query to a .SQL file (here
call it Result1.sql) and executing an aggregating query against this .SQL
file.

  SELECT SUM(Calc) AS Total
  FROM "Result1.sql"

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:null values in Sum() queries


Hi,

This is how I do to sum up a number of rows with possible null values:

(sum(nvl(column_name, 0)))

This statement converts null values to 0:s, which is what you want to do,
right?

regards,

/Kiina

TQuadrado, Lda. skrev i meddelandet <7kajse$on...@forums.borland.com>...

Quote
>>No, null and zero are not the same. Null means undefined. Something plus
>>undefined is undefined. Something minus undefined is undefined. This is
>>correct behavior according to the SQL standard.

>>Bill

>yes i know. But in the case of Number field types, and when using aggregate
>functions like Sum(), should not SQL standars assume null as zero ? I
tried,
>but i can't get an explanation for it.

>regards,

>Pedro MG

Re:null values in Sum() queries


On Fri, 18 Jun 1999 09:28:04 +0200, "Kristina Sirhuber" <ki...@home.se>
wrote:

Quote
>This is how I do to sum up a number of rows with possible null values:

>(sum(nvl(column_name, 0)))

>This statement converts null values to 0:s, which is what you want to do,
>right?

This is a good attempt at a solution, but it has two fatal problems. First,
I am not sure that standard SQL (defined here as SQL-92) includes a NVL
function; I cannot find such a reference. Second, I can say for sure that
local SQL does not include such a function. The table is a Paradox table,
so local SQL rules apply.

So the solution would not only not work, but would also result in an
EDBEngineError exception.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:null values in Sum() queries


It would be nice if Local SQL supported the SQL92 COALESCE
function then we could write SUM(COALESCE(<field>,0)). This
was actually the effect of using SUM in Delphi 1 because QBE
defaults to treating Null as zero for numeric fields.

There's a lot to be said for using QBE on Paradox and dBase
tables.

And I *still* don't think Local SQL handles OR clauses
properly.

Nick Spurrier (MoDESoft, UK)

Re:null values in Sum() queries


On Sat, 19 Jun 1999 18:00 +0100 (BST), n...@mode.co.uk (Nick Spurrier)
wrote:

Quote
>It would be nice if Local SQL supported the SQL92 COALESCE
>function then we could write SUM(COALESCE(<field>,0)). This
>was actually the effect of using SUM in Delphi 1 because QBE
>defaults to treating Null as zero for numeric fields.

I agree. It is already on my prioritized "What Local SQL Needs List". And I
keep harping on this around the office, here.

Quote
>There's a lot to be said for using QBE on Paradox and dBase
>tables.

Plus you can mix-n-match SQL and QBE in local SQL statements. You can
execute a SELECT statement against a QBE query saved to file (a text file
containing the QBE query, not the QBE query's result set).

Quote
>And I *still* don't think Local SQL handles OR clauses
>properly.

Example? And you statement takes into account using parentheses to control
or override the default order of precedence of the logical operators?

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:null values in Sum() queries


Quote
>>Example?

It's when the ORs are not single choices.
"WHERE ((InvoiceDate < :date) AND ((HasCredit="False") OR
(CustomerDelisted="True")))" works OK.
But
"WHERE ((InvoiceDate < :date) AND (((HasCredit="False) OR
(SomeOtherOr Statement)) OR ((CustomerDelisted="True") OR
(AnotherOrStatement))))" does not return the required result.
You still have to restate the "(InvoiceDate etc ) AND" in the
second multiple OR, just as you had to in BDE 2.52. I've
written a visual query builder and I had to work around that.

Also, while you're there Steve, is the problem of parameters
in sub-selects being ignored going to be fixed?

Nick Spurrier (MoDESoft, UK)

Re:null values in Sum() queries


On Tue, 22 Jun 1999 22:31 +0100 (BST), n...@mode.co.uk (Nick Spurrier)
wrote:

Quote
>>>Example?

>It's when the ORs are not single choices.
>"WHERE ((InvoiceDate < :date) AND ((HasCredit="False") OR
>(CustomerDelisted="True")))" works OK.
>But
>"WHERE ((InvoiceDate < :date) AND (((HasCredit="False) OR
>(SomeOtherOr Statement)) OR ((CustomerDelisted="True") OR
>(AnotherOrStatement))))" does not return the required result.
>You still have to restate the "(InvoiceDate etc ) AND" in the
>second multiple OR, just as you had to in BDE 2.52. I've
>written a visual query builder and I had to work around that.

I would need to look at this in more detail.

Quote
>Also, while you're there Steve, is the problem of parameters
>in sub-selects being ignored going to be fixed?

No, I cannot say. I only work on the BDE (including local SQL)
peripherally. My main area of responsibility is in Delphi.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Other Threads