SQL null problem

[Tommi Bauer]

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]]

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.

 

[Daniel Pratt]

Hi Tommi,

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]

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