Answered by:
handle empty value in SUM function

Hello all,
I am stucked at the point of handling empty value in my stored procedure (query). I am using SUM function to add all the values in a column against some criteria. If an empty value is returned (i.e no row but not NULL) the sum function prints null in that column. I want that if so is the situation then i should get 0(zero) so that in my calculation (SUM [Column1] + SUM [Column2]),if first SUM is 0 (which is not happening yet and i want so) and the second SUM is greater than 1 result(e.g 100,200,300 etc) then the final result (SUM [Column1] + SUM[Column2]) should be (0+100) or (0+200) ,which is equal to 100 or 200 etc. But now the final result is NULL if only one SUM returns no row (or empty if i may say). Final result is nonnull only when both SUM return greater than or equal to 0 (zero) values.
Here is my query i am using:
SELECT SUM(CONVERT(money, CASE ITSS_AcTransactions.Debit WHEN 0 THEN Amount WHEN 1 THEN 0 END)) AS crd, AcID
FROM ITSS_AcTransactions
WHERE (TransPaymentDate <= CONVERT(DateTime, @Param2)) AND (TransPaymentDate >= CONVERT(DateTime, @Param1)) AND (AcID = @Param3)
GROUP BY AcID, Amount
Waiting for a quick reply
Thanks in advance
maiqbal
Question
Answers

I think SUM will not return NULL, you can check the definition of SUM function at http://msdn.microsoft.com/enus/library/ms187810(SQL.90).aspx. I suggest to use in this may SUM(IsNull(value, 0)) instead of IsNull(Sum(), 0).
 Marked as answer by John C GordonMicrosoft employee, Moderator Wednesday, April 08, 2009 6:04 PM

ISNULL can definitely work. I don't how you try it.
please refer to
http://msdn.microsoft.com/enus/library/ms184325.aspx, SUM(a,0) means if a=NULL, it returns 0.
here is the simpliest sample:
SELECT ISNULL(SUM([col1]),0)
FROM [leoyu].[dbo].[testsum] where col2=2 Marked as answer by John C GordonMicrosoft employee, Moderator Wednesday, April 08, 2009 6:04 PM
All replies

Hi
try using ISNULL() function as follows:
SELECT ISNULL(SUM(CONVERT(money, CASE ITSS_AcTransactions.Debit WHEN 0 THEN Amount WHEN 1 THEN 0 END)),0) AS crd, AcID
FROM ITSS_AcTransactions
WHERE (TransPaymentDate <= CONVERT(DateTime, @Param2)) AND (TransPaymentDate >= CONVERT(DateTime, @Param1)) AND (AcID = @Param3)
GROUP BY AcID, Amount
Thanks
Sreekar 

I think SUM will not return NULL, you can check the definition of SUM function at http://msdn.microsoft.com/enus/library/ms187810(SQL.90).aspx. I suggest to use in this may SUM(IsNull(value, 0)) instead of IsNull(Sum(), 0).
 Marked as answer by John C GordonMicrosoft employee, Moderator Wednesday, April 08, 2009 6:04 PM

ISNULL can definitely work. I don't how you try it.
please refer to
http://msdn.microsoft.com/enus/library/ms184325.aspx, SUM(a,0) means if a=NULL, it returns 0.
here is the simpliest sample:
SELECT ISNULL(SUM([col1]),0)
FROM [leoyu].[dbo].[testsum] where col2=2 Marked as answer by John C GordonMicrosoft employee, Moderator Wednesday, April 08, 2009 6:04 PM

Thanks to both Ying & Leo for your replies.
But it is not working here.
Actually let me clear the problem even more;the SUM(Column1) has no data when query is run.
Here is the portion of the query i am having problem:

(
SELECT
SUM(CONVERT(money, CASE ITSS_AcTransactions.Debit WHEN 1 THEN Amount WHEN 0 THEN 0 END)
)
AS debit
FROM
ITSS_AcTransactions
WHERE
(ITSS_AcTransactions.TransPaymentDate <= CONVERT(DateTime,@todate) AND ITSS_AcTransactions.TransPaymentDate >=
CONVERT(DateTime,@fromdt) AND ITSS_AcTransactions.AcId = ITSS_Accounts.AcId)
GROUP
BY ITSS_AcTransactions.AcId
)+(
SELECT
SUM(CONVERT(money,
CASE ITSS_AcTransactions.Debit WHEN 0 THEN Amount WHEN 1 THEN 0 END)) AS credit
FROM
ITSS_AcTransactions
WHERE
(ITSS_AcTransactions.TransPaymentDate <= CONVERT(DateTime,@todate) AND ITSS_AcTransactions.TransPaymentDate >=
CONVERT(DateTime,@fromdt) AND ITSS_AcTransactions.AcId = ITSS_Accounts.AcId)
GROUP
BY ITSS_AcTransactions.AcId
)
as BalanceDS

Both the SELECTs are actually subqueries.
First Select's SUM function returns no data,whic is as below:

COLUMN NAME(with SUM)
.....................
<< nothing shown here

If you get the problem 100% clear now,please suggest how should i handle/solve this?
Thanks indeed
maiqbal