none
Error SUMming values that include null values as well RRS feed

  • Question

  • I am having problem with following query:

     

     

     

    Here object P contains Q. However, Q may be missing for some P. Q has an Amount attribute. I am trying to sum that amount in Q belonging at the level of P.
    Since Q.amount is numeric, PTotal is also of numeric type and it does not accept null values and I get an error. I tried to use the above expression but it does not work either and I get the same error. Can someone help me fix this?
    Thanks

    var aList = from P in DataContext.P
    
    where P.Id == SelectedId
    
    orderby P.Name ascending
    
    select
    
    new { PId = P.Id, Name = P.Name, PAmount = P.Amount, PTotal = P.Q.Sum(o => (o.Amount == null ? 0 : o.Amount)) };

     

    Wednesday, November 25, 2009 9:47 PM

Answers

  • Hello Khalique,

     

    Welcome to LINQ to SQL forum!

     

    The Null values could be at the P.Q since Q may be missing for some P as you have described.  Please try the following query to check the P.Q.Count first. 

    ================================================================================================
    var aList = from p in db.Ps

                where p.ID == selectedId

                orderby p.Name

                select new

                {

                    PID = p.ID,

                    Name = p.Name,

                    PAmount = p.Amount,

                    PTotal = p.Qs.Count > 0 ? p.Qs.Sum(q => q.Amount ?? 0) : 0

                };
    ================================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, November 26, 2009 1:14 AM
    Moderator
  • Hello Khalique,

     

    The query works fine at my side. 

    =============================================================================
                    var query = from a in db.As

                                orderby a.Name

                                select new

                                {

                                    ID = a.ID,

                                    Name = a.Name,

                                    Amounts = a.Bs.Count > 0 ? (a.Bs.Sum(b => b.Cs.Count > 0 ? b.Cs.Sum(c => c.Amount) : 0)) : 0

                                };
    =============================================================================

     

    I recommend you check the T-SQL generated by the query {db.Log = Console.Out;}.

     

    Here is the T-SQL generated by my LINQ query:

    =============================================================================
    SELECT [t0].[ID], [t0].[Name],

        (CASE

            WHEN ((

                SELECT COUNT(*)

                FROM [dbo].[B] AS [t1]

                WHERE [t1].[AID] = [t0].[ID]

                )) > @p0 THEN (

                SELECT SUM([t5].[value])

                FROM (

                    SELECT

                        (CASE

                            WHEN ((

                                SELECT COUNT(*)

                                FROM [dbo].[C] AS [t3]

                                WHERE [t3].[BID] = [t2].[ID]

                                )) > @p1 THEN (

                                SELECT SUM([t4].[Amount])

                                FROM [dbo].[C] AS [t4]

                                WHERE [t4].[BID] = [t2].[ID]

                                )

                            ELSE @p2

                         END) AS [value], [t2].[AID]

                    FROM [dbo].[B] AS [t2]

                    ) AS [t5]

                WHERE [t5].[AID] = [t0].[ID]

                )

            ELSE @p3

         END) AS [Amounts]

    FROM [dbo].[A] AS [t0]

    ORDER BY

    t0].[Name]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    =============================================================================

    Did you receive any exception?   Or you got the wrong sum results? 

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Khalique Thursday, December 3, 2009 7:01 PM
    Wednesday, December 2, 2009 6:29 AM
    Moderator

All replies

  • Hello Khalique,

     

    Welcome to LINQ to SQL forum!

     

    The Null values could be at the P.Q since Q may be missing for some P as you have described.  Please try the following query to check the P.Q.Count first. 

    ================================================================================================
    var aList = from p in db.Ps

                where p.ID == selectedId

                orderby p.Name

                select new

                {

                    PID = p.ID,

                    Name = p.Name,

                    PAmount = p.Amount,

                    PTotal = p.Qs.Count > 0 ? p.Qs.Sum(q => q.Amount ?? 0) : 0

                };
    ================================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, November 26, 2009 1:14 AM
    Moderator
  • Hello Khalique,

     

    How is the problem now?   If you need any further assistance, please feel free to let me know.
     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 1, 2009 12:28 AM
    Moderator
  • Thanks Lingzhi. It worked.
    I saw your proposed solution right after you posted it but because of the holidays I couldn't test it earlier.
    Now I have annother related question.
    I want to sum amount two levels deep. Lets say object A has zero or more Bs and each B has zero or more C. C has an Amount property. I want to show the total C.Amount at the level of A. In other words I want to do TotalAmountAtA = A.Bs.Sum(q.Cs.Sum(C.Amount))
    I tried the same logic as follows (it compiled but did not show me the sum:
    TotalAmountAtA = A.Bs.Count > 0 ? (A.Bs.Sum(q => q.Cs.Count > 0 ? q.Cs.Sum(r => r.Amount) : 0)) : 0
    What is the correct method to perform such an operation?
    Thanks for your help.

    Tuesday, December 1, 2009 5:34 PM
  • Hello Khalique,

     

    The query works fine at my side. 

    =============================================================================
                    var query = from a in db.As

                                orderby a.Name

                                select new

                                {

                                    ID = a.ID,

                                    Name = a.Name,

                                    Amounts = a.Bs.Count > 0 ? (a.Bs.Sum(b => b.Cs.Count > 0 ? b.Cs.Sum(c => c.Amount) : 0)) : 0

                                };
    =============================================================================

     

    I recommend you check the T-SQL generated by the query {db.Log = Console.Out;}.

     

    Here is the T-SQL generated by my LINQ query:

    =============================================================================
    SELECT [t0].[ID], [t0].[Name],

        (CASE

            WHEN ((

                SELECT COUNT(*)

                FROM [dbo].[B] AS [t1]

                WHERE [t1].[AID] = [t0].[ID]

                )) > @p0 THEN (

                SELECT SUM([t5].[value])

                FROM (

                    SELECT

                        (CASE

                            WHEN ((

                                SELECT COUNT(*)

                                FROM [dbo].[C] AS [t3]

                                WHERE [t3].[BID] = [t2].[ID]

                                )) > @p1 THEN (

                                SELECT SUM([t4].[Amount])

                                FROM [dbo].[C] AS [t4]

                                WHERE [t4].[BID] = [t2].[ID]

                                )

                            ELSE @p2

                         END) AS [value], [t2].[AID]

                    FROM [dbo].[B] AS [t2]

                    ) AS [t5]

                WHERE [t5].[AID] = [t0].[ID]

                )

            ELSE @p3

         END) AS [Amounts]

    FROM [dbo].[A] AS [t0]

    ORDER BY

    t0].[Name]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    =============================================================================

    Did you receive any exception?   Or you got the wrong sum results? 

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Khalique Thursday, December 3, 2009 7:01 PM
    Wednesday, December 2, 2009 6:29 AM
    Moderator
  • Thanks again Lingzhi. It worked. I think I had a typo in my code or something. However, your suggestion gave me the hint to check it agian.
    I appreciate your help
    kr
    Thursday, December 3, 2009 7:00 PM
  • It's my pleasure!  :)

    Have a nice weekend!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, December 4, 2009 12:39 AM
    Moderator