Re: SQL null problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tommi Bauer (mospam_at_hotmsn.com)
Date: 05/17/04 

Date: Mon, 17 May 2004 19:40:01 GMT

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

"Daniel Pratt" <kolREMOVETHISkata_is@hotmail.com> wrote in message
news:%23oRl$REPEHA.268@TK2MSFTNGP11.phx.gbl...
> Hi Tommi,
>
> "Tommi Bauer" <mospam@hotmsn.com> wrote in message
> news:J48qc.50737$YmM1.48545@news04.bloor.is.net.cable.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
>
>

Relevant Pages

  • Re: Batch commands & Ms-Access
    ... due to the Access DataBase's query enging which doesn't support multiple ... Thanks & Regards, ... Microsoft Online Support ... |>> It is not only Access, you cannot use Batch SQL commands in ADONET. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: GROUP BY...
    ... > Dominique Javet wrote: ... >> Regards, Dom ... > These two are completely unrelated SQL commands. ... > query, which you didn't. ...
    (comp.lang.php)
  • Re: Syntax Error - INSERT INTO Statement
    ... ok, now create a new query, switch to SQL view and paste this SQL there, access will tell you what is going wrong ... Best regards, ... "Alex Dybenko" wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL null problem
    ... Thanks a lot Nicholas and Daniel. ... >> Regards, ... > That's the defined behavior of the SQL SUM function. ... > Assuming you are querying SQL Server, you could write the query thus to ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Poor performance when executing stored procedure
    ... Best regards and thanks again for your help! ... If the query ... does your table by any chance have a nonclustered index, ... > Daniel Walzenbach wrote: ...
    (microsoft.public.sqlserver.programming)