Results 1 to 11 of 11

Thread: SQL SUM - help

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Question Unanswered: SQL SUM - help

    Hello all

    I need help, whenever there is null involved , i want the result to be null
    How can I do that ?
    Thanks in advance.

    create table #test (a int null , b int )

    insert into #Test values (1 , 1)
    insert into #Test values (2 , 2)
    insert into #Test values (3 , 3 )
    insert into #Test values (4 , 3 )
    insert into #Test values (5 , null)

    select sum(b),avg(b) from #test

    Current Result
    ==============
    18 2

    Expected Result
    ==============
    NULL NULL

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    select sum(IsNull(b, 0)),avg(IsNull(b, 0)) from #test
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Were you thinking about something like this?

    select
    case when exists(select 1 from #test where a is null or b is null)
    then null
    else sum(a)
    end
    ,case when exists(select 1 from #test where b is null or b is null)
    then null
    else sum(b)
    end
    from #test

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Sorry, I gotta learn how to read one of these days...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Frettmaestro
    Sorry, I gotta learn how to read one of these days...
    Sleepless in London ?

  6. #6
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Heh, seems like it...I have myself the first full weekend off (including friday!!) for the first time since christmas and what am I doing...? Looking through all the forums I have ever been into...damn, I gotta get me a life soon...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Frettmaestro
    Heh, seems like it...I have myself the first full weekend off (including friday!!) for the first time since christmas and what am I doing...? Looking through all the forums I have ever been into...damn, I gotta get me a life soon...
    It is good weekend idea for real programmer...

  8. #8
    Join Date
    Feb 2004
    Posts
    3

    Re: SQL SUM - help

    drop table #test
    create table #test (a1 int null , b1 int )

    insert into #Test values (1 , 1)
    insert into #Test values (2 , 2)
    insert into #Test values (3 , 3 )
    insert into #Test values (3 , 3 )
    insert into #Test values (3 , 3 )
    insert into #Test values (4 , 3 )
    insert into #Test values (4 , 4 )
    insert into #Test values (5 , null)
    insert into #Test values (5 , 2 )


    select a1 ,
    case when exists( select 1 from #test t1 where t1.b1 is null and t1.a1 = t2.a1 ) then null
    else sum(b1)
    end as SumTotal ,
    case when exists( select 1 from #test t1 where t1.b1 is null and t1.a1 = t2.a1 ) then null
    else avg(b1)
    end Averge
    from #test t2
    group by a1

    Expected Result:

    a1 SumTotal Averge
    ----------- ----------- -----------
    1 1 1
    2 2 2
    3 9 3
    4 7 3
    5 NULL NULL

    (5 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.

    This gives correct result.

    Any other Suggestion ...

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: SQL SUM - help

    Originally posted by kannan_007
    drop table #test
    create table #test (a1 int null , b1 int )

    insert into #Test values (1 , 1)
    insert into #Test values (2 , 2)
    insert into #Test values (3 , 3 )
    insert into #Test values (3 , 3 )
    insert into #Test values (3 , 3 )
    insert into #Test values (4 , 3 )
    insert into #Test values (4 , 4 )
    insert into #Test values (5 , null)
    insert into #Test values (5 , 2 )


    select a1 ,
    case when exists( select 1 from #test t1 where t1.b1 is null and t1.a1 = t2.a1 ) then null
    else sum(b1)
    end as SumTotal ,
    case when exists( select 1 from #test t1 where t1.b1 is null and t1.a1 = t2.a1 ) then null
    else avg(b1)
    end Averge
    from #test t2
    group by a1

    Expected Result:

    a1 SumTotal Averge
    ----------- ----------- -----------
    1 1 1
    2 2 2
    3 9 3
    4 7 3
    5 NULL NULL

    (5 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.

    This gives correct result.

    Any other Suggestion ...
    What final result do you want to have?

  10. #10
    Join Date
    Feb 2004
    Posts
    3

    Talking Re: SQL SUM - help

    Originally posted by snail
    What final result do you want to have?
    I took your suggestion. Modified little bit , I got the result What I wanted that is
    a1 SumTotal Averge
    ----------- ----------- -----------
    1 1 1
    2 2 2
    3 9 3
    4 7 3
    5 NULL NULL

    Basically, I 'm trying to sum and avg the value based on col1 ,
    if there is any null value for Col2 , then the result for the col1 should be null for SUM and AVG.

    In general, if we use SUM or AVG , it excludes the NULL , and tries to do the calculation.

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: SQL SUM - help

    Originally posted by kannan_007
    I took your suggestion. Modified little bit , I got the result What I wanted that is
    a1 SumTotal Averge
    ----------- ----------- -----------
    1 1 1
    2 2 2
    3 9 3
    4 7 3
    5 NULL NULL

    Basically, I 'm trying to sum and avg the value based on col1 ,
    if there is any null value for Col2 , then the result for the col1 should be null for SUM and AVG.

    In general, if we use SUM or AVG , it excludes the NULL , and tries to do the calculation.
    Good luck !

Posting Permissions

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