Using Aggregate functions in DataView filters

c# datatable dataview aggregate-functions filter
Shrewdroid picture Shrewdroid · May 24, 2010 · Viewed 10.6k times · Source

i have a DataTable that has a column ("Profit"). What i want is to get the Sum of all the values in this table. I tried to do this in the following manner...

DataTable dsTemp = new DataTable();

dsTemp.Columns.Add("Profit");

DataRow dr = null;

dr = dsTemp.NewRow();
dr["Profit"] = 100;
dsTemp.Rows.Add(dr);

dr = dsTemp.NewRow();
dr["Profit"] = 200;
dsTemp.Rows.Add(dr);

DataView dvTotal = dsTemp.DefaultView;
dvTotal.RowFilter = " SUM ( Profit ) ";

DataTable dt = dvTotal.ToTable();

But i get an error while applying the filter... how can i get the Sum of the Profit column in a variable

thank you...

Answer

fearofawhackplanet picture fearofawhackplanet · May 24, 2010

Set the datacolumn to a numeric type (int, decimal, whatever):

DataColumn col = new DataColumn("Profit", typeof(int));
dsTemp.Columns.Add(col);

Use Compute:

int total = dsTemp.Compute("Sum(Profit)", "");

Note that aggregation is not a type of filter, which is the main problem with the approach you are tyring.

Related questions

Easiest way to copy a dataview to a datatable in C#?

I need to copy a dataview into a datatable. It seems like the only way to do so is to iterate through the dataview item by item and copy over to a datatable. There has to be a better way.

Read More
How to bind dataGridView predefined columns with columns from sql statement (without adding new columns)?

Is there a elegant way, to bind predefined dataGridView columns with results from a SQL statement? Example: dataGridView1.Columns.Add("EID", "ID"); dataGridView1.Columns.Add("FName", "FirstName"); Some SQL like SELECT t.FirstName AS FName, t.EmpID AS EID FROM …

Read More
Sorted dataview to datatable

I have the following method: private DataTable getsortedtable(DataTable dt) { dt.DefaultView.Sort = "Name desc"; //I would need to return the datatable sorted. } My issue is that I cannot change the return type of this method and I have to …

Read More