SUM with NULL

I want to SUM records. Some of their fields are NULL.
Instead of ignoring the NULL I would like the result of the SUM is NULL.

I know there is an option to declare before the action but I forgot which one !?

Thank you for your HELP !

102rine

what does your table look like and what fields are you summing on?
what database are you using?

The database is a Foreign Trade database. So I'm adding figures for aggregation.

For example I have the fields for a certain commodity code and a certain country
partnerCode TradeValue Qty Weight
111 12345 2 NULL
111 234 1 1
The SUM gives me
partnerCode TradeValue Qty Weight
111 12579 3 1
instead of
partnerCode TradeValue Qty Weight
111 12579 3 NULL

Any suggestion !?
Thank you
102rine

for Oracle here is a method that could be used.

just to understand the inputs:
> create table trade (partnercode number, tradevalue number, qty number, weight number);
> insert into trade values (111,12345,2,null);
> insert into trade values (111,234,1,1);
> commit;
> select partnercode,SUM(tradevalue) tradevalue, SUM(qty) qty, SUM(weight) weight
2 from trade group by partnercode;

PARTNERCODE TRADEVALUE QTY WEIGHT
----------- ---------- ---------- ----------
111 12579 3 1

sounds like you want to return the 'NULL' character string if there are any NULL values in the grouping, else return the summed value. here is a method for the qty column:

> select partnercode, tradevalue, qty, decode(decodeweight,0,to_char(qty),'NULL') weight
from (select partnercode,SUM(tradevalue) tradevalue, SUM(qty) qty,
SUM(weight) weight, SUM(decode(weight,NULL,1,0)) decodeweight
from trade group by partnercode)

PARTNERCODE TRADEVALUE QTY WEIGHT
----------- ---------- ---------- ------------
111 12579 3 NULL

use ISNULL function

is the ISNULL a SQL Server or Oracle or some other database function?

i would enjoy seeing an example

thanks MAK

The problem is not too change the NULL valeur by something else but to change the other record which matches with it (due to the 'group by') by the NULL value.


Question to jkoopman:
What exactly does the decode in ORACLE when you do this : SUM(decode(weight,NULL,1,0)) ?

Thank you
102rine

thought you were asking how to sum columns together where one or more might be null and therefore wanted to display a null value or description in the place of a true SUM function on that column.

the decode does nothing to the data only interogates a column for a null value. in the SQL provided it then assigns a "flag" to the rows that have a null value so that when we actually want to report on the column we have summed we can check the flag and determine if we want to display the summed values or a null string.

in effect this is changing the output of the group by for a specific value. it is not changing the null values or the true numeric values.

here is a thought and a question.
does the null value in one row actually mean that the other values are invalid?
usually a null value means that there just was not an assigned value for that column on that row. if this is a true data issue someone should change the application to not insert rows that contain nulls or you can change the column default value to be not null. this idea is why a SUM of columns does not error out or produce invalid results .

What exactly does the decode in ORACLE sorry didn't really answer the question :
when you do this : SUM(decode(weight,NULL,1,0)) ?

this is the flag i talked about in the last post. it basically states that add one to the sum if a null is seen else add zero. then we can interogate the value of this column latter and see if it is zero or greater than zero. if it is zero then we know that we have not seen any null values on the "weight" column.

ALSO noticed i mis-keyed the SQL.
should be
> select partnercode, tradevalue, qty, decode(decodeweight,0,to_char(weight),'NULL') weight
from (select partnercode,SUM(tradevalue) tradevalue, SUM(qty) qty,
SUM(weight) weight, SUM(decode(weight,NULL,1,0)) decodeweight
from trade group by partnercode)

What I meant is use IS NULL clause. My apologies for the confusion.

In SQL Server.....
use tempdb
go
create table mytable (partnerCode int, TradeValue int, Qty int, Weight int)
go
insert into mytable select 111, 12345, 2, NULL
insert into mytable select 111, 234, 1, 1
insert into mytable select 111, 234, 21, 11
go
select partnercode,
tradevalue =case when sum(tradevaluenull)=0 then sum(tradevalue) else NULL end ,
Quantity=case when sum(qtynull)=0 then sum(qty) else NULL end ,
Weight =case when sum(weightnull)=0 then sum(weight) else NULL end from
(
select partnercode,tradevalue,qty,weight,
case when tradevalue is NULL then 1 else 0 end as tradevaluenull,
case when qty is NULL then 1 else 0 end as qtynull ,
case when weight is NULL then 1 else 0 end as weightnull
from mytable
) as A group by partnercode

MAK,
Thanks!
thought it would be something like that.
Cheers!

That sounds great with the small sample.
I will try it now in the real table :)

Thank you so much !
If I have the implicit option to avoid all this code I will keep you informed.

102rine