How to Calculate Sum (Total) of Datatable Columns Using C#

How to calculate the sum of the datatable column in asp.net?

To calculate the sum of a column in a DataTable use the DataTable.Compute method.

Example of usage from the linked MSDN article:

DataTable table = dataSet.Tables["YourTableName"];

// Declare an object variable.
object sumObject;
sumObject = table.Compute("Sum(Amount)", string.Empty);

Display the result in your Total Amount Label like so:

lblTotalAmount.Text = sumObject.ToString();

how to calculate sum (Total) of DataTable Columns using C#

Instead of dt.Compute you can also use LINQ:

int sum = dt.AsEnumerable().Sum(r => r.Field<int>("Salary"));

The nice thing with LINQ is that it's so easy to calculate the everage instead:

double average = dt.AsEnumerable().Average(r => r.Field<int>("Salary"));

or to get max salary:

int maxSalary = dt.AsEnumerable().Max(r => r.Field<int>("Salary"));

or to filter, count all salaries which are higher than 3000:

int countHighSalary = dt.AsEnumerable().Count(r => r.Field<int>("Salary") >= 3000);

How to sum columns in a dataTable?

Try this:

            DataTable dt = new DataTable();
            int sum = 0;
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    sum += (int)dr[dc];
                }
            } 

Get SUM of DataTable column

You can use DataTable.Compute method

var sumOfA = dt.Compute("Sum(Call)", "[Affiliate Name] = 'A'");

Or use LINQ

var sumOfA = dt.AsEnumerable()
               .Where(dr => dr.Field<string>("Affiliate Name").Equals("A"))
               .Sum(dr => dr.Field<int>("Call"));

In case you want get sum of calls for all values of Affiliate Name use LINQ with anonymous type

var results = dt.AsEnumerable()
                .GroupBy(dr => dr.Field<string>("Affiliate Name"))
                .Select((group, dr) => new 
                {
                    AffiliateName = group.Key,
                   CallsCount = group.Sum(dr.Field<int>("Call")))
                });

foreach(var result in results)
{
    // Use results
    Console.WriteLine($"{result.AffiliateName}: {result.CallsCount}");
}

calculating the sum total of the columns and display in footer when colum names are dynamic

You can loop all the columns and rows in the DataTable and show the results in the footer.

DataTable dt = new DAL_Reports().Rpt_Count_Transpose(from_date, to_date);

//create a array to store the total column values
int[] RowTotals = new int[dt.Columns.Count];

//loop all the rows in the datatable
foreach (DataRow row in dt.Rows)
{
    //loop all the columns in the row
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        //add the values for each cell to the total
        RowTotals[i] += Convert.ToInt32(row[dt.Columns[i].ColumnName]);
    }
}

//loop all the columns again to set the values in the footer
for (int i = 0; i < dt.Columns.Count; i++)
{
    GridView1.FooterRow.Cells[i].Text = string.Format("{0:N2}", RowTotals[i]);
}

How to get the sum of multiple columns of a datatable in c#

If all columns are strings but actually represent floating point numbers:

double totalSum = dt.AsEnumerable()
    .Sum(r=> double.Parse(r[7].ToString()) + double.Parse(r[9].ToString()) + double.Parse(r[10].ToString()));

If some are empty or have invalid numbers you need to use double.TryParse.

GroupBy columns of DataTable and calculate sum of string column

Obviously, the ideal solution would be to fill the data table with doubles (or, even better: decimals) instead of strings in the first place, but I'll assume that you are working with a legacy system where that is not possible.

Currently, you copy the whole data table just to change the data type of two columns. You can avoid that by converting the values when summing over them:

var result = dtTimephasedStatusTemp.AsEnumerable().
    GroupBy(x => new { TaskId = x.Field<string>("Task Id") }).
    Select(x => new
    {
        TaskId = x.Key.TaskId,
        TaskActualWorkSum = x.Sum(y => Double.Parse(y.Field<string>("TaskActualWork"))),
        TaskWorkSum = x.Sum(y => Double.Parse(y.Field<string>("TaskWork")))
    }); 

Alternatively, you might want to consider adding a new column to your data table and filling it in a loop. That, also, might be more efficient than copying the complete table.

Sum column where (condition) with datatable

My result is 30.

var dt = new DataTable();
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("money", typeof(int));

var row = dt.NewRow();
row["Date"] = new DateTime(2020, 5, 3);
row["money"] = 20;
dt.Rows.Add(row);

row = dt.NewRow();
row["Date"] = new DateTime(2020, 5, 3);
row["money"] = 10;
dt.Rows.Add(row);

row = dt.NewRow();
row["Date"] = new DateTime(2020, 8, 3);
row["money"] = 5;
dt.Rows.Add(row);

row = dt.NewRow();
row["Date"] = new DateTime(2020, 9, 7);
row["money"] = 56;
dt.Rows.Add(row); 

row = dt.NewRow();
row["Date"] = new DateTime(2020, 2, 11);
row["money"] = 45;
dt.Rows.Add(row);

var sumOfValuesInMarch = dt.AsEnumerable()
    .Where(x => x.Field<DateTime>("Date").Month == 5)
    .Sum(x => x.Field<int>("money"))
    .ToString(); // 30

---

Related Topics