How to add subtotal in datatbale RSS

• • Reply
  

  ajeesh.mekka...

  Member

  22 Points

  54 Posts

  How to add subtotal in datatbale

  May 04, 2012 12:08 PM|LINK

  Hi

  i have datatable like

  Name   Mark

  Emp1     40

  Emp1     70

  Emp2     30

  Emp2     50

  I need a new datatable like

  Name   Mark

  Emp1     40

  Emp1     70

               110 ---------Subtotal of Emp1

  Emp2     30

  Emp2     50

               80  ---------Subtotal of Emp2

• • Reply
  

  urenjoy

  Star

  12181 Points

  1824 Posts

  Re: How to add subtotal in datatbale

  May 04, 2012 12:14 PM|LINK

  See following using Linq:

  http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/89437f51-6254-4d50-ac1d-75bc46f32034/

  http://techbrij.com
• • Reply
  

  kedarrkulkar...

  All-Star

  34247 Points

  5505 Posts

  Re: How to add subtotal in datatbale

  May 04, 2012 12:24 PM|LINK

  try these steps... suppose, datatbale dtOriginal contains the data.

  DataSet dsOne = new DataSet();
  DataSet dsTwo = new DataSet();
  
  //split dtOriginal into two dataset based on value of Name column
  dsOne.Tables.Add(dtOriginal.Select("Name='Emp1'").CopyToDataTable());
  dsTwo.Tables.Add(dtOriginal.Select("Name='Emp1'").CopyToDataTable());
  
  //add new row with total field in both datasets
  DataRow row1 = dsOne.Tables[0].NewRow();
  row1["Mark"] = dtOriginal.Compute("Sum(Mark)", "Name ='Emp1'");
  dsOne.Tables[0].Rows.Add(row1);
  
  DataRow row2 = dsTwo.Tables[0].NewRow();
  row2["Mark"] = dtOriginal.Compute("Sum(Mark)", "Name ='Emp2'");
  dsTwo.Tables[0].Rows.Add(row2);

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
  • Edited by kedarrkulkarni on May 04, 2012 12:24 PM
  • Edited by kedarrkulkarni on May 04, 2012 12:25 PM
• • Reply
  

  ajeesh.mekka...

  Member

  22 Points

  54 Posts

  Re: How to add subtotal in datatbale

  May 04, 2012 12:27 PM|LINK

  i don't want subtotal seperatly.i need original values then subtotal..please my sample

• • Reply
  

  tusharrs

  Contributor

  3230 Points

  668 Posts

  Re: How to add subtotal in datatbale

  May 04, 2012 12:27 PM|LINK

  protected void Page_Load(object sender, EventArgs e)
      {
          DataTable dt = new DataTable();
          dt.Columns.Add("name");
          dt.Columns.Add("marks", System.Type.GetType("System.Int16"));
          dt.Rows.Add("emp1", 10);
          dt.Rows.Add("emp1", 80);
          dt.Rows.Add("emp2", 10);
          dt.Rows.Add("emp2", 50);
          dt.Rows.Add("emp3", 120);
          dt.Rows.Add("emp4", 50);
          Int16 marks = 0;
          for (int i = 0; i <= dt.Rows.Count - 1; i++)
          {
              if (i > 0)
              {
                  if (dt.Rows[i]["name"].ToString().ToLower() != dt.Rows[i - 1]["name"].ToString().ToLower())
                  {
                      dt.Rows.InsertAt(dt.NewRow(),i);
                      dt.Rows[i]["marks"] = marks;
                      marks = 0;
                      i++;
                  }
              }
              marks += Convert.ToInt16(dt.Rows[i]["marks"]);
              
          }
          dt.Rows.Add(dt.NewRow());
          dt.Rows[dt.Rows.Count - 1]["marks"] = marks;
          
      }

  Regards
  
  ( Mark as Answer if it helps you out )
  
  View my Blog
  • Marked as answer by Catherine Shan - MSFT on May 14, 2012 02:24 AM
• • Reply
  

  Mastan Oli

  Contributor

  5088 Points

  998 Posts

  Re: How to add subtotal in datatbale

  May 04, 2012 01:28 PM|LINK

  Why dont you try by LINQ??? very easy... here the solution for your req...

  DataTable dtObject = new DataTable();
          
          dtObject.Columns.Add("Employee");
          dtObject.Columns.Add("Value", typeof(int));
  
          dtObject.Rows.Add("emp1", 40);
          dtObject.Rows.Add("emp1", 70);
          dtObject.Rows.Add("emp2", 30);
          dtObject.Rows.Add("emp2", 50);
  
  
          (from x in dtObject.AsEnumerable()
           group x by x.Field<string>("Employee") into grp
           select new
           {
               Key = grp.Key,
               Sum = grp.Sum(S => S.Field<int>("Value"))
           }).ToList().ForEach(T => dtObject.Rows.Add(T.Key + "Total", T.Sum));
  
  //If you want to order use following also...
  
          dtObject = dtObject.AsEnumerable().OrderBy(O => O.Field<string>("Employee")).CopyToDataTable();

  
  Result

  emp1  40
  emp1  70
  emp1Total  110
  emp2  30
  emp2  50
  emp2Total  80

  Mãsthän Oli
  playingOOPS | மெய்ப்பொருள் காண்பதறிவு
  Mark as Answer If you find helpful
  • Marked as answer by Catherine Shan - MSFT on May 14, 2012 02:24 AM