CODEWITHSUNDEEP
c#vb.net
chobo
chobo

Reputation: 32281

How to sum columns in a dataTable?

How can I get a sum for all the columns in a datatable? Say I had the following table. How can I calculate the "total" row? It should be easy to add total row to a datatable.

         Columns    hits     uniques    sigups, etc...
Rows                  
1                      12         1         23
2                       1         0          5
3                       6         2          9


total                  19          3        37

Update

I ended up with this. It was the only thing I could get to work.

 For Each col As DataColumn In TotalsTable.Columns    
    If col.DataType.Name = "DateTime" Then
            count = count + 1
                Continue For
        End If

        Dim colTotal As Double = 0
        Dim value As Double

        For Each row As DataRow In TotalsTable.Rows
            If Double.TryParse(row(col), value) Then
                    colTotal += Double.Parse(row(col))
                    End If
        Next

    totalRow(count) = colTotal
        count = count + 1

  Next

Upvotes: 15

Views: 113380

Answers (6)

Dy Say
Dy Say

Reputation: 11

 for (int i=0;i<=dtB.Columns.Count-1;i++)
 {
   array(0, i) = dtB.Compute("SUM([" & dtB.Columns(i).ColumnName & "])", "")                   
 }

Upvotes: 1

ealef
ealef

Reputation: 554

It's a pity to use .NET and not use collections and lambda to save your time and code lines This is an example of how this works: Transform yourDataTable to Enumerable, filter it if you want , according a "FILTER_ROWS_FIELD" column, and if you want, group your data by a "A_GROUP_BY_FIELD". Then get the count, the sum, or whatever you wish. If you want a count and a sum without grouby don't group the data 

var groupedData = from b in yourDataTable.AsEnumerable().Where(r=>r.Field<int>("FILTER_ROWS_FIELD").Equals(9999))
                          group b by b.Field<string>("A_GROUP_BY_FIELD") into g
                          select new
                          {
                              tag = g.Key,
                              count = g.Count(),
                              sum = g.Sum(c => c.Field<double>("rvMoney"))
                          };

Upvotes: 2

Nicolas
Nicolas

Reputation: 1171

There is also a way to do this without loops using the DataTable.Compute Method. The following example comes from that page. You can see that the code used is pretty simple.:

private void ComputeBySalesSalesID(DataSet dataSet)
{
    // Presumes a DataTable named "Orders" that has a column named "Total."
    DataTable table;
    table = dataSet.Tables["Orders"];

    // Declare an object variable. 
    object sumObject;
    sumObject = table.Compute("Sum(Total)", "EmpID = 5");
}

I must add that if you do not need to filter the results, you can always pass an empty string:

sumObject = table.Compute("Sum(Total)", "")

Upvotes: 44

Jay Riggs
Jay Riggs

Reputation: 53595

You can loop through the DataColumn and DataRow collections in your DataTable:

// Sum rows.
foreach (DataRow row in dt.Rows) {
    int rowTotal = 0;
    foreach (DataColumn col in row.Table.Columns) {
        Console.WriteLine(row[col]);
        rowTotal += Int32.Parse(row[col].ToString());
    }
    Console.WriteLine("row total: {0}", rowTotal);
}
// Sum columns.
foreach (DataColumn col in dt.Columns) {
    int colTotal = 0;
    foreach (DataRow row in col.Table.Rows) {
        Console.WriteLine(row[col]);
        colTotal += Int32.Parse(row[col].ToString());
    }
    Console.WriteLine("column total: {0}", colTotal);
}

Beware: The code above does not do any sort of checking before casting an object to an int.

EDIT: add a DataRow displaying the column sums

Try this to create a new row to display your column sums:

DataRow totalsRow = dt.NewRow();
foreach (DataColumn col in dt.Columns) {
    int colTotal = 0;
    foreach (DataRow row in col.Table.Rows) {
        colTotal += Int32.Parse(row[col].ToString());
    }
    totalsRow[col.ColumnName] = colTotal;
}
dt.Rows.Add(totalsRow);

This approach is fine if the data type of any of your DataTable's DataRows are non-numeric or if you want to inspect the value of each cell as you sum. Otherwise I believe @Tim's response using DataTable.Compute is a better.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460048

I doubt that this is what you want but your question is a little bit vague

Dim totalCount As Int32 = DataTable1.Columns.Count * DataTable1.Rows.Count

If all your columns are numeric-columns you might want this:

You could use DataTable.Compute to Sum all values in the column.

 Dim totalCount As Double
 For Each col As DataColumn In DataTable1.Columns
     totalCount += Double.Parse(DataTable1.Compute(String.Format("SUM({0})", col.ColumnName), Nothing).ToString)
 Next

After you've edited your question and added more informations, this should work:

 Dim totalRow = DataTable1.NewRow
 For Each col As DataColumn In DataTable1.Columns
     totalRow(col.ColumnName) = Double.Parse(DataTable1.Compute("SUM(" & col.ColumnName & ")", Nothing).ToString)
 Next
 DataTable1.Rows.Add(totalRow)

Upvotes: 5

ysrb
ysrb

Reputation: 6740

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

Upvotes: 8

Related Questions