none
handle empty value in SUM function

    Question

  • 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 non-null 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
    Tuesday, April 07, 2009 9:37 AM

Answers

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
    Tuesday, April 07, 2009 9:49 AM
  • Thanks SREEKAR for your immediate response.
    I used ISNULL as you suggested but nothing is happening with this too, really. I used it exactly as you wrote to me.
    Please any more suggestion.....
    Thanks indeed
    maiqbal
    Tuesday, April 07, 2009 9:57 AM
  • I think SUM will not return NULL, you can check the definition of SUM function at http://msdn.microsoft.com/en-us/library/ms187810(SQL.90).aspx. I suggest to use in this may SUM(IsNull(value, 0)) instead of IsNull(Sum(), 0).
    Tuesday, April 07, 2009 1:11 PM
  • ISNULL can definitely work. I don't how you try it.
    please refer to
    http://msdn.microsoft.com/en-us/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
    Wednesday, April 08, 2009 1:50 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 sub-queries.
    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
    Thursday, April 09, 2009 5:00 AM