Results 1 to 12 of 12

Thread: SUM with NULL

  1. #1
    Join Date
    Apr 2005
    Location
    Paris
    Posts
    4

    Question 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

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    what does your table look like and what fields are you summing on?
    what database are you using?

  3. #3
    Join Date
    Apr 2005
    Location
    Paris
    Posts
    4
    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

  4. #4
    Join Date
    Mar 2003
    Posts
    468
    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

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  6. #6
    Join Date
    Mar 2003
    Posts
    468
    is the ISNULL a SQL Server or Oracle or some other database function?

    i would enjoy seeing an example

    thanks MAK

  7. #7
    Join Date
    Apr 2005
    Location
    Paris
    Posts
    4
    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

  8. #8
    Join Date
    Mar 2003
    Posts
    468
    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 .

  9. #9
    Join Date
    Mar 2003
    Posts
    468
    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)

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  11. #11
    Join Date
    Mar 2003
    Posts
    468
    MAK,
    Thanks!
    thought it would be something like that.
    Cheers!

  12. #12
    Join Date
    Apr 2005
    Location
    Paris
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •