SQL null problem

Discussion in 'ASP .Net' started by Tommi Bauer, May 17, 2004.

  1. Tommi Bauer

    Tommi Bauer Guest

    I am writing a query in ASP.NET C#,

    select sum(size) as total_size from my_table where is_valid = 1

    However, if there is no data in the database, it returns 1 record with
    value null. How come it doesn't return 0 instead? because I am using
    "sum", if SQL can't find any record.. isn't it suppose to return 0?

    In my table setup, the default value for the "size" field is 0 and doesn't
    allow NULL.

    Regards,

    T.
     
    Tommi Bauer, May 17, 2004
    #1
    1. Advertising

  2. Tommi,

    The reason this happens is that most (I believe) operations in sql will
    return null when one of the operands is null. You have to account for this.
    Basically, you want to do:

    select isnull(sum(size), 0) as total_size from my_table where is_valid = 1

    Hope this helps.

    --
    - Nicholas Paldino [.NET/C# MVP]
    -


    "Tommi Bauer" <> wrote in message
    news:J48qc.50737$...
    > I am writing a query in ASP.NET C#,
    >
    > select sum(size) as total_size from my_table where is_valid = 1
    >
    > However, if there is no data in the database, it returns 1 record with
    > value null. How come it doesn't return 0 instead? because I am using
    > "sum", if SQL can't find any record.. isn't it suppose to return 0?
    >
    > In my table setup, the default value for the "size" field is 0 and doesn't
    > allow NULL.
    >
    > Regards,
    >
    > T.
    >
    >
     
    Nicholas Paldino [.NET/C# MVP], May 17, 2004
    #2
    1. Advertising

  3. Tommi Bauer

    Daniel Pratt Guest

    Hi Tommi,

    "Tommi Bauer" <> wrote in message
    news:J48qc.50737$...
    > I am writing a query in ASP.NET C#,
    >
    > select sum(size) as total_size from my_table where is_valid = 1
    >
    > However, if there is no data in the database, it returns 1 record with
    > value null. How come it doesn't return 0 instead? because I am using
    > "sum", if SQL can't find any record.. isn't it suppose to return 0?
    >
    > In my table setup, the default value for the "size" field is 0 and doesn't
    > allow NULL.
    >
    > Regards,


    That's the defined behavior of the SQL SUM function. It make sense
    because you really can't some "nothing". It also allows you to distinguish
    between having n number of values that sum to 0 and having 0 values.
    Assuming you are querying SQL Server, you could write the query thus to
    remove the null:

    select isnull(sum(size),0) as total_size from my_table where
    is_valid = 1

    By the way, this is definitely more of a SQL question than an ASP.NET or
    C# question. The microsoft.public.sqlserver.programming newsgroup is a good
    choice for questions on this topic.

    Regards,
    Daniel
     
    Daniel Pratt, May 17, 2004
    #3
  4. Tommi Bauer

    Tommi Bauer Guest

    Thanks a lot Nicholas and Daniel. Really appreciate your solutions and
    explanation, it works!




    "Daniel Pratt" <> wrote in message
    news:%23oRl$...
    > Hi Tommi,
    >
    > "Tommi Bauer" <> wrote in message
    > news:J48qc.50737$...
    > > I am writing a query in ASP.NET C#,
    > >
    > > select sum(size) as total_size from my_table where is_valid = 1
    > >
    > > However, if there is no data in the database, it returns 1 record with
    > > value null. How come it doesn't return 0 instead? because I am using
    > > "sum", if SQL can't find any record.. isn't it suppose to return 0?
    > >
    > > In my table setup, the default value for the "size" field is 0 and

    doesn't
    > > allow NULL.
    > >
    > > Regards,

    >
    > That's the defined behavior of the SQL SUM function. It make sense
    > because you really can't some "nothing". It also allows you to distinguish
    > between having n number of values that sum to 0 and having 0 values.
    > Assuming you are querying SQL Server, you could write the query thus to
    > remove the null:
    >
    > select isnull(sum(size),0) as total_size from my_table where
    > is_valid = 1
    >
    > By the way, this is definitely more of a SQL question than an ASP.NET

    or
    > C# question. The microsoft.public.sqlserver.programming newsgroup is a

    good
    > choice for questions on this topic.
    >
    > Regards,
    > Daniel
    >
    >
     
    Tommi Bauer, May 17, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kivak Wolf
    Replies:
    2
    Views:
    10,077
    Kivak Wolf
    Jun 28, 2005
  2. vizlab
    Replies:
    3
    Views:
    4,214
    Michael Bar-Sinai
    Oct 17, 2007
  3. Replies:
    16
    Views:
    7,346
    Mike Schilling
    Oct 12, 2005
  4. Replies:
    5
    Views:
    26,709
    Mike Schilling
    Mar 29, 2006
  5. ecoolone
    Replies:
    0
    Views:
    767
    ecoolone
    Jan 3, 2008
Loading...

Share This Page