locked
sum values in datatable using linq based on conditions RRS feed

  • Question

  • User-1888080761 posted

    Hi,

    I'm having a datatable like mentioned below.

    ID   Percentage  
    1        50
    1        30
    2        0
    2        100  

    Result:

    ID   Percentage  
    1        80
    2       100

    I tried this and it doesn't work

    var n =  dt.AsEnumerable().Where(r => (int)r["ID"] != "0" || (int)r["ID"] != "100").
    Sum(r => (int)r["Percentage"]);

    Now I need to sum the percentage for each ID and the percentage for each ID should be 0 or 100 percentage. 

    If any one of the ID in table doesn't have 0 or 100 I need to alert. Pls suggest me how I can do this in linq and I think its the best way.

    Thursday, April 23, 2015 3:59 AM

Answers

  • User1577371250 posted

    Hi,

    try this

    var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => int.Parse(s["Percentage"].ToString()))
                                  }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2015 8:03 AM

All replies

  • User1577371250 posted

    Hi,

    Check this query.

    DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Percentage", typeof(int));
    
                table.Rows.Add(1, 50);
                table.Rows.Add(1, 30);
                table.Rows.Add(2, 0);
                table.Rows.Add(2, 100);
    
                var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                                  }).ToList();

    Thursday, April 23, 2015 4:10 AM
  • User-1888080761 posted

    Hi,

    Check this query.

    DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Percentage", typeof(int));
    
                table.Rows.Add(1, 50);
                table.Rows.Add(1, 30);
                table.Rows.Add(2, 0);
                table.Rows.Add(2, 100);
    
                var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                                  }).ToList();

    Thanks for the prompt reply. How can I alert if the conditions get satisfied.

    Thursday, April 23, 2015 4:14 AM
  • User1577371250 posted

    Hi,

    Can you post some sample data for that?

    i didn't get your point here.

    Thursday, April 23, 2015 4:15 AM
  • User-657100335 posted
    foreach (var item in result)
    {
       if (item.Percentage != 0 || item.Percentage != 100)
          MessageBox.Show("Your alert here");
    }

    Try adding this after Lokesh's answer for the alert.

    Thursday, April 23, 2015 4:22 AM
  • User-1888080761 posted

    Hi,

    Can you post some sample data for that?

    i didn't get your point here.

    The below is the result we get after checking the condition.

    If sum of any of the ID doesn't have 0 or 100 then I need to alert. Also can ID be considered as string.

    ID   Percentage  
    1        80
    2       100
    Thursday, April 23, 2015 5:10 AM
  • User1577371250 posted

    try this

     DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(string));
                table.Columns.Add("Percentage", typeof(int));
    
                table.Rows.Add("1", 50);
                table.Rows.Add("1", 30);
                table.Rows.Add("2", 0);
                table.Rows.Add("2", 100);
    
                var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                                  }).ToList();
    
    
                foreach (var item in result)
                {
                    if (item.Percentage != 0 || item.Percentage == 100)
                    {
                        string message = item.ID + " percentage is " + item.Percentage;
                        // Show the message in ALERT
                    }
                }

    Thursday, April 23, 2015 5:27 AM
  • User-1888080761 posted

    try this

     DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(string));
                table.Columns.Add("Percentage", typeof(int));
    
                table.Rows.Add("1", 50);
                table.Rows.Add("1", 30);
                table.Rows.Add("2", 0);
                table.Rows.Add("2", 100);
    
                var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                                  }).ToList();
    
    
                foreach (var item in result)
                {
                    if (item.Percentage != 0 || item.Percentage == 100)
                    {
                        string message = item.ID + " percentage is " + item.Percentage;
                        // Show the message in ALERT
                    }
                }

    I think its close. Can I have percentage as int?

    Thursday, April 23, 2015 5:37 AM
  • User1577371250 posted

    Hi,

    Can I have percentage as int?

    It's already INT.

                table.Columns.Add("Percentage", typeof(int));
    

     

    Thursday, April 23, 2015 5:52 AM
  • User-1888080761 posted

    Hi,

    vignesht5

    Can I have percentage as int?

    It's already INT.

                table.Columns.Add("Percentage", typeof(int));
    

     

    In this part I need to have percentage as int.

     select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                                  }).ToList();

    Thursday, April 23, 2015 5:57 AM
  • User1577371250 posted

    Hi,

    try this

    var result = (from p in table.AsEnumerable()
                              group p by p["ID"]
                                  into r
                                  select new
                                  {
                                      ID = r.Key,
                                      Percentage = r.Sum((s) => int.Parse(s["Percentage"].ToString()))
                                  }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2015 8:03 AM