Sum() And Nulls

24 Nov 2005 5:36 PM

CJM

I have an SP that produces some summary figures, including count() and sum()
values:

Select
(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
Where I.InvoiceDate >= @DateFrom
and I.InvoiceDate <= @DateTo
and (O.LocationID = @LocationID or @LocationID = 0)
and O.CustomerID = @CustomerID) as NumInvClean,

(Select Sum(CleanPrice)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
Where I.InvoiceDate >= @DateFrom
and I.InvoiceDate <= @DateTo
and (O.LocationID = @LocationID or @LocationID = 0)
and O.CustomerID = @CustomerID) as SumInvClean

This example code calculates the Number of Clean Invoices sent, and the
summary of the value of those invoices. However, if there are no Clean
Invoices sent within the reporting period, the Count() returns 0 (as
expected) but the Sum() returns Null. This Null is correct since Sum ignores
all Null values, but it's not really helpful to me - I want this to return 0
in this case.

How do I do about doing this?

I've tried (and failed) using a Case statement and IsNull. I suspect IsNull
is the answer though I havent managed to get the syntax correct if it is...

Any ideas?

Thanks in advance

CJM

--
cjmnew***@REMOVEMEyahoo.co.uk
[remove the obvious bits]

24 Nov 2005 5:53 PM

CJM

Actually, I've just managed to get IsNull working:

Select
(IsNull((Select Sum(CleanPrice)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
Where I.InvoiceDate >= @DateFrom
and I.InvoiceDate <= @DateTo
and (O.LocationID = @LocationID or @LocationID = 0)
and O.CustomerID = @CustomerID) ,0)) as SumInvClean,

etc

It was just a case of getting the bracketing syntax correct.

CJM

24 Nov 2005 6:26 PM

Jaime Lucci

Or

(Select Sum(isnull(CleanPrice,0))
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
Where I.InvoiceDate >= @DateFrom
and I.InvoiceDate <= @DateTo
and (O.LocationID = @LocationID or @LocationID = 0)
and O.CustomerID = @CustomerID) as SumInvClean

Show quote

"CJM" <cjmnews04@newsgroup.nospam> wrote in message
news:OrSyT2R8FHA.4076@tk2msftngp13.phx.gbl...
> I have an SP that produces some summary figures, including count() and
sum()
> values:
>
> Select
> (Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID) as NumInvClean,
>
> (Select Sum(CleanPrice)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID) as SumInvClean
>
> This example code calculates the Number of Clean Invoices sent, and the
> summary of the value of those invoices. However, if there are no Clean
> Invoices sent within the reporting period, the Count() returns 0 (as
> expected) but the Sum() returns Null. This Null is correct since Sum
ignores
> all Null values, but it's not really helpful to me - I want this to return
0
> in this case.
>
> How do I do about doing this?
>
> I've tried (and failed) using a Case statement and IsNull. I suspect
IsNull
> is the answer though I havent managed to get the syntax correct if it
is...
>
> Any ideas?
>
> Thanks in advance
>
> CJM
>
> --
> cjmnew***@REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>

25 Nov 2005 9:20 AM

CJM

"Jaime Lucci" <jaimelu***@hotmail.com> wrote in message
news:Oqjd2TS8FHA.1248@TK2MSFTNGP14.phx.gbl...
> Or
>
> (Select Sum(isnull(CleanPrice,0))
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID) as SumInvClean
>

Jaime,

I had tried this but for me it still returns Null rather than £0.00 when
there are no invoices. I'll check and have another go.

Thanks

Chris

24 Nov 2005 10:18 PM

Hugo Kornelis

On Thu, 24 Nov 2005 17:36:15 -0000, CJM wrote:

Show quote

>I have an SP that produces some summary figures, including count() and sum()
>values:
>
>Select
>(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID) as NumInvClean,
>
> (Select Sum(CleanPrice)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID) as SumInvClean
>
>This example code calculates the Number of Clean Invoices sent, and the
>summary of the value of those invoices. However, if there are no Clean
>Invoices sent within the reporting period, the Count() returns 0 (as
>expected) but the Sum() returns Null. This Null is correct since Sum ignores
>all Null values, but it's not really helpful to me - I want this to return 0
>in this case.
>
>How do I do about doing this?
>
>I've tried (and failed) using a Case statement and IsNull. I suspect IsNull
>is the answer though I havent managed to get the syntax correct if it is...
>
>Any ideas?
>
>Thanks in advance
>
>CJM

Hi CJM,

I am aware that you already solved this. But there's amore efficient way
to achieve the same results:

Select Count(*) AS NumInvClean, Sum(CleanPrice) AS SumInvClean
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
Where I.InvoiceDate >= @DateFrom
and I.InvoiceDate <= @DateTo
and (O.LocationID = @LocationID or @LocationID = 0)
and O.CustomerID = @CustomerID

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

25 Nov 2005 9:19 AM

CJM

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:1veco1dii846m38jaekuu4o3tqb1f8liig@4ax.com...

> Hi CJM,
>
> I am aware that you already solved this. But there's amore efficient way
> to achieve the same results:
>
> Select Count(*) AS NumInvClean, Sum(CleanPrice) AS SumInvClean
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join Invoices I on I.InvoiceID = D.CleanInvoiceID
> Where I.InvoiceDate >= @DateFrom
> and I.InvoiceDate <= @DateTo
> and (O.LocationID = @LocationID or @LocationID = 0)
> and O.CustomerID = @CustomerID
>

Hugo,

I had tried this originally, but hit a problem. I'll have another look at it
and see if I can crack it.

Thanks

25 Nov 2005 9:47 PM

Hugo Kornelis

On Fri, 25 Nov 2005 09:19:02 -0000, CJM wrote:

(snip)

>Hugo,
>
>I had tried this originally, but hit a problem. I'll have another look at it
>and see if I can crack it.
>
>Thanks
>

Hi CJM,

If it still doesn't work, then please post table structure (as CREATE
TABLE statements), sample data (as INSERT statements), expected results,
acquired results, and the EXACT query you used.

Also, check out www.aspfaq.com/5006.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)