locked
Grouping by and also adding a sum RRS feed

  • Question

  • Hi there,

    I am really stuck with a linq problem and really getting nowhere.

               foreach (var detail in _rewardDetail)
                {
                    //how many rewards the customer has accurred
                    int rewardAccured = 0;
                    //use linq to group the rewards so we can count how many rewards have been made
                    //first check if there are any rewards if there are no rewards then it means we have a new customer
                    if (_customerReward != null)
                    {
                        var groupReward =
                      from r in _customerReward
                      where r.RedeemedDate == DateTime.MinValue
                      group r by r.StoreRewardID into g
                     //group r by new{ r.StoreRewardID,r.PurchaseAmount} into g
                      select new { StoreRewardID = g.Key, RewardCount = g.Count(), PurchaseAmount = "1.00" or Sum r.PurchaseAmount };
    
                        foreach (var item in groupReward)
                        {
                            //ensure we only update the correct reward ID
                            if (item.StoreRewardID == detail.RewardID)
                            {
                                rewardAccured = item.RewardCount;
                                detail.PurchaseAmount = item.PurchaseAmount;
                            }
                        }
                        //_rewa

    WHat I am doing is a group by StoreRewardID which works fine. I now want to add the field PurchaseAmount into my select. How can I add a sum formula into my new Select?

    I have hardcoded "1.00" just for testing and it works fine but I need to be able to use r.PurchaseAmount.

    my linq knowledge is pretty average as you can see but I can't for the life of me work out how to sum purchaseAmount.

    Can anyone suggest what I should be doing?


    Monday, December 7, 2009 12:18 AM

Answers

  • Hello,

     

    If you want to retrieve the Sum of the PurchaseAmount in each groups, you can refer to this query:

    ============================================================================================
    select new { StoreRewardID = g.Key, RewardCount = g.Count(), PurchaseAmount = g.Sum(cr => cr.PurchaseAmount) }
    ============================================================================================

     

    Or if you want to get the sum of all the customerReward objects, you many consider imbedding another query into the select new one:

    ============================================================================================
    select new { StoreRewardID = g.Key, RewardCount = g.Count(), PurchaseAmount = _customerReward.Sum(cr => cr.PurchaseAmount) }
    ============================================================================================

     

    BTW, I don’t think the current issue is directly related to LINQ to XML or XML, you can consider posting such LINQ questions in other related data platform queues, http://social.msdn.microsoft.com/Forums/en-US/category/dataplatformdev.

     

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun


    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.
    Monday, December 7, 2009 2:15 AM
    Moderator

All replies

  • Hello,

     

    If you want to retrieve the Sum of the PurchaseAmount in each groups, you can refer to this query:

    ============================================================================================
    select new { StoreRewardID = g.Key, RewardCount = g.Count(), PurchaseAmount = g.Sum(cr => cr.PurchaseAmount) }
    ============================================================================================

     

    Or if you want to get the sum of all the customerReward objects, you many consider imbedding another query into the select new one:

    ============================================================================================
    select new { StoreRewardID = g.Key, RewardCount = g.Count(), PurchaseAmount = _customerReward.Sum(cr => cr.PurchaseAmount) }
    ============================================================================================

     

    BTW, I don’t think the current issue is directly related to LINQ to XML or XML, you can consider posting such LINQ questions in other related data platform queues, http://social.msdn.microsoft.com/Forums/en-US/category/dataplatformdev.

     

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun


    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.
    Monday, December 7, 2009 2:15 AM
    Moderator
  • Hello,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    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 11, 2009 1:13 AM
    Moderator