如何在不同的组上平均求和 [英] how to perform sum on average on distinct different then the group field

问题描述

问题在于计算出的SumCustomerDebt.

The problem is the calculated SumCustomerDebt.

我需要根据CustomerLocation进行分组，但是CustomerDebt在表中重复多次(对于每个客户，具有不同的值).我需要计算SumCustomerDebt-每个客户的CustomerDebt之和.对于每个客户，CustomerDebt是CustomerDebt的平均值.

I need to group according to the CustomerLocation, but the CustomerDebt repeat multiple times in the table (for each customer, with different values). I need to calculate SumCustomerDebt - as sum of CustomerDebt of each customer. For each customer the CustomerDebt is average of CustomerDebt.

我该怎么办?

这是我的C#代码.

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"] = 80;
    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);

    }

}

打印的信息应包含2行:
1)"CustomerLocation = Chichago，SumSubTotal = 80，OrderCount = 3，SumCustomerDebt = 150"-> SumCustomerDebt等于140 = 90(对于CustomerID = 1(100 + 80)/2平均)+ 50(对于CustomerID = 2)
2)"CustomerLocation = Miami，SumSubTotal = 50，OrderCount = 1，SumCustomerDebt = 20"

the printed info should contain 2 lines:
1) "CustomerLocation=Chichago,SumSubTotal=80,OrderCount=3, SumCustomerDebt=150" -> SumCustomerDebt equal 140 = 90 (for CustomerID=1 (100+80)/2 average) + 50 (for CustomerID=2)
2) "CustomerLocation=Miami,SumSubTotal=50,OrderCount=1, SumCustomerDebt=20"

推荐答案

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.GroupBy(r=> r.Field<int>("CustomerID"))
                                           .Sum(g => g.Average(r=> r.Field<int>("CustomerDebt"))),
                  };


    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);    
    }
}

这是输出

CustomerLocation=Chichago,SumSubTotal=80,OrderCount=3,SumCustomerDebt=140
CustomerLocation=Miami,SumSubTotal=50,OrderCount=1,SumCustomerDebt=20

这篇关于如何在不同的组上平均求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！