Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SQL null problem

Reply
Thread Tools

SQL null problem

 
 
Tommi Bauer
Guest
Posts: n/a
 
      05-17-2004
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.


 
Reply With Quote
 
 
 
 
Nicholas Paldino [.NET/C# MVP]
Guest
Posts: n/a
 
      05-17-2004
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]
- http://www.velocityreviews.com/forums/(E-Mail Removed)


"Tommi Bauer" <(E-Mail Removed)> wrote in message
news:J48qc.50737$(E-Mail Removed) ble.rogers.com...
> 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.
>
>



 
Reply With Quote
 
 
 
 
Daniel Pratt
Guest
Posts: n/a
 
      05-17-2004
Hi Tommi,

"Tommi Bauer" <(E-Mail Removed)> wrote in message
news:J48qc.50737$(E-Mail Removed) ble.rogers.com...
> 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


 
Reply With Quote
 
Tommi Bauer
Guest
Posts: n/a
 
      05-17-2004
Thanks a lot Nicholas and Daniel. Really appreciate your solutions and
explanation, it works!




"Daniel Pratt" <(E-Mail Removed)> wrote in message
news:%23oRl$(E-Mail Removed)...
> Hi Tommi,
>
> "Tommi Bauer" <(E-Mail Removed)> wrote in message
> news:J48qc.50737$(E-Mail Removed) ble.rogers.com...
> > 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
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
createImage sometime returns null and sometime returns non-null. vizlab Java 3 10-17-2007 11:21 AM
"stringObj == null" vs "stringObj.equals(null)", for null check?? qazmlp1209@rediffmail.com Java 5 03-29-2006 10:37 PM
difference between null object and null string gokul.b@gmail.com Java 16 10-12-2005 06:43 PM
VB.NET Null to SQL Null (ASP.NET 2.0 GridView) Kivak Wolf ASP .Net 2 06-28-2005 02:01 PM



Advertisments