how to perform sum on distinct different then the group field

Question

bellow is the C# code.

the problem is the calculated SumCustomerDebt.

i need to group according to the CustomerLocation, but the CustomerDebt repeat multiple times in the table (for each customer). i need to calculate SumCustomerDebt - as sum of CustomerDebt according to distinct CustomerID. how can i do it?

protected DataTable generateData()
{
    DataTable dt = new DataTable();
    DataColumn column1 = new DataColumn("salesId", Type.GetType("System.Int32"));
    dt.Columns.Add(column1);
    DataColumn column2 = new DataColumn("CustomerLocation", Type.GetType("System.String"));
    dt.Columns.Add(column2);
    DataColumn column3 = new DataColumn("CustomerID", Type.GetType("System.Int32"));
    dt.Columns.Add(column3);
    DataColumn column4 = new DataColumn("CustomerDebt", Type.GetType("System.Int32"));
    dt.Columns.Add(column4);
    DataColumn column5 = new DataColumn("SubTotal", Type.GetType("System.Int32"));
    dt.Columns.Add(column5);


    DataRow dr = dt.NewRow();
    dr["salesId"] = 1;
    dr["CustomerLocation"] = "Chichago";
    dr["CustomerID"] = 1;
    dr["CustomerDebt"] = 100;
    dr["SubTotal"] = 10;
    dt.Rows.Add(dr);

    dr = dt.NewRow();
    dr["salesId"] = 2;
    dr["CustomerLocation"] = "Chichago";
    dr["CustomerID"] = 1;
    dr["CustomerDebt"] = 100;
    dr["SubTotal"] = 40;
    dt.Rows.Add(dr);

    dr = dt.NewRow();
    dr["salesId"] = 3;
    dr["CustomerLocation"] = "Chichago";
    dr["CustomerID"] = 2;
    dr["CustomerDebt"] = 50;
    dr["SubTotal"] = 30;
    dt.Rows.Add(dr);

    dr = dt.NewRow();
    dr["salesId"] = 4;
    dr["CustomerLocation"] = "Miami";
    dr["CustomerID"] = 3;
    dr["CustomerDebt"] = 20;
    dr["SubTotal"] = 50;
    dt.Rows.Add(dr);

    return dt;

}

protected void test()
{
    DataTable dt = generateData();

    var result = from row in dt.AsEnumerable()
                  group row by new
                  {
                      CustomerLocation = row.Field<string>("CustomerLocation"),
                  } into grp
                  select new
                  {
                      CustomerLocation = grp.Key.CustomerLocation,
                      SumSubTotal = grp.Sum(r => r.Field<int>("SubTotal")),
                      OrderCount = grp.Count(),
                      SumCustomerDebt = grp.Sum(r => r.Field<int>("CustomerDebt")),  //this is incorrect, how can i calculate it?
                  };


    foreach (var item in result)
    {
        string info = string.Format("CustomerLocation={0},SumSubTotal={1},OrderCount={2},SumCustomerDebt={3}", item.CustomerLocation, item.SumSubTotal, item.OrderCount, item.SumCustomerDebt);
        Console.WriteLine(info);

    }

}

Can you provide expected `SumCustomerDebt` value for your sample data? — Sergey Berezovskiy, Nov 06 '13 at 08:23
the printed info should contain 2 lines: 1) "CustomerLocation=Chichago,SumSubTotal=80,OrderCount=3, SumCustomerDebt=150" -> SumCustomerDebt equal 150 = 100 (for CustomerID=1 even if the customer appear twice) + 50 (for CustomerID=2) 2) "CustomerLocation=Miami,SumSubTotal=50,OrderCount=1, SumCustomerDebt=20" — user2455595, Nov 06 '13 at 12:13

Demir · Answer 1 · 2013-11-06T13:16:56.543

1

You can try this:

    var Group = dt.AsEnumerable().GroupBy(x => x["CustomerLocation"].ToString()).ToDictionary(x => x.Key, x => x.ToList());

    foreach (var Items in Group)
    {
        foreach (var DistinctCustomerID in Items.Value.Select(x => x["CustomerID"].ToString()).Distinct())
{
            // Gets first instance only
            var Instance = dt.AsEnumerable().Where(x => x[DistinctCustomerID] == DistinctCustomerID).FirstOrDefault();

            int SumCustomerDebt = Convert.ToInt32(Instance["CustomerDebt"].ToString());

            var Customer = new { CustomerLocation = Items.Key, SumSubTotal = Instance["SumSubTotal"].ToString(), OrderCount = Instance["OrderCount"].ToString(), SumCustomerDebt = SumCustomerDebt };

            Console.WriteLine("You can Print Customer object's Properties");
        }
    }

edited Nov 06 '13 at 13:16

answered Nov 06 '13 at 08:23

Demir

1,787
1
29
42

i need to return result as in the original code, which contain information on the group (like SumSubTotal) and also the SumCustomerDebt. how can i do it in the original code? – user2455595 Nov 06 '13 at 12:00
in addition, the code bellow doesn't do distinct, we need to get first Instance in Instances and not run foreach, otherwise, we will summaries the same customer more then once. – user2455595 Nov 06 '13 at 12:09
@user2455595: I edited the answer according to your comments. – Demir Nov 06 '13 at 13:19
thanks Demir, but this doesn't return the data as I need. lazyberezovsky solution is exactly what i need. – user2455595 Nov 06 '13 at 18:39

score 0 · Accepted Answer · answered Nov 06 '13 at 12:59

Group location rows by customer id and select debt of first customer from each group:

var query = from r in generateData().AsEnumerable()
            group r by r.Field<string>("CustomerLocation") into g
            select new
            {
                CustomerLocation = g.Key,
                SumSubTotal = g.Sum(r => r.Field<int>("SubTotal")),
                OrderCount = g.Count(),
                SumCustomerDebt = 
                      g.GroupBy(r => r.Field<int>("CustomerID"))
                       .Sum(cg => cg.First().Field<int>("CustomerDebt"))
            };

This produces your expected results:

{ CustomerLocation = Chichago, SumSubTotal = 80, OrderCount = 3, SumCustomerDebt = 150 } { CustomerLocation = Miami, SumSubTotal = 50, OrderCount = 1, SumCustomerDebt = 20 }

hello lazyberezovsky, i seems the data and function is different than what i tough in the beginning so i need to do something more complected. i opened another question because the request is different and this was answered already. can you please have a look on the new question? http://stackoverflow.com/questions/19887512/how-to-perform-sum-on-average-on-distinct-different-then-the-group-field — user2455595, Nov 10 '13 at 08:04
@user2455595 just replace `cg.First().Field("CustomerDebt")` with `cg.Average(r => r.Field("CustomerDebt")` — Sergey Berezovskiy, Nov 10 '13 at 09:55

how to perform sum on distinct different then the group field

2 Answers2