DataTable need to sum a column with filter on it

Question

I have a data table in C# with columns:

Product|Status|ShipedVolume|TotalVolume

A     New        0           10     
A     New        5           20
B     Closed     7           20

I want to sum (TotalVolume-ShipedVolume) filtered by product and Status.

For example I want to query how many not shipped item I have for product A, in this example the answer is 25. The same question for product B will be 0.

How can I code it?

score 3 · Accepted Answer · answered Dec 26 '13 at 14:12

Try this, assuming your number columns type is int:

var query = (from t in table.AsEnumerable()
             where t["Status"].ToString().Trim() != "Closed" 
                  && t["Product"].ToString().Trim() == "B"
             select Convert.ToInt32(t["TotalVolume"]) 
                 - Convert.ToInt32(t["ShipedVolume"])).Sum();

score 2 · Answer 2 · answered Dec 26 '13 at 13:51

Example of usage from MSDN :

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

// Declare an object variable.
object sumObject;
sumObject = table.Compute("Sum(Amount) order by name", "");

Display the result in your Total Amount Label like so:

lblTotalAmount.Text = sumObject.ToString();

score 1 · Answer 3 · edited Jan 02 '14 at 15:08

This works. Note that you can't create SUM expression on the difference directly - See note below:

//fill table from data source ... not shown.

//setup desired filter criteria
string strExpr = "Product = 'B' AND Status= 'New'";
//Add a new column to the table to perform hold the difference of each row
//You have to do this since the sum expreession can't use more than 1 column
//See: Remarks section of: http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx
myTable.Columns.Add("Diff", typeof(Int32), "TotalVolume - ShipedVolume");
Object sumDiffColumn = myTable.Compute("sum(Diff)",strExpr);

Int32 result=-1;
//check to see if the filter returned empty rows or not before cast is performed
if (sumDiffColumn is DBNull)
{
    result = 0;
}
else
{
    result = Convert.ToInt32(sumDiffColumn);
}

MessageBox.Show("Sum of Difference:"+result.ToString()+ " for filter:"+strExpr);

DataTable need to sum a column with filter on it

3 Answers3