2

I have a data table and a column contains int values. I have not specified the column with any datatype. When I perform the following.

object sum = dttest.Compute("sum(Value)", "");

I am getting below error.

Invalid usage of aggregate function Sum() and Type: String.

And I tried converting column value to int using

object sum = dttest.Compute("sum(Convert(Value, 'System.Int32'))","");

again I am getting another error

Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

When I specify the datatype for the column, first code will return correct value. But in my case I can't specify column datatype. Any one having solution ?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Xavier Dennis
  • 39
  • 1
  • 1
  • 7

4 Answers4

10

You can use LINQ to Datatable:

 var result = dttest.AsEnumerable()
                    .Sum(x => Convert.ToInt32(x["Value"]));

Example how to Sum for specific name:

 var result = dttest.AsEnumerable()
                    .Where(r => r.Field<string>("Name") == "FilterName")
                    .Sum(x => Convert.ToInt32(x["Value"]));
cuongle
  • 74,024
  • 28
  • 151
  • 206
1

try

object sum = dttest.Compute("Sum(Value)", "[Value] IS NOT NULL");

Sample code 

    static DataTable GetTable()
    {
        DataTable table = new DataTable();
        table.Columns.Add("Value", typeof(int));
        table.Columns.Add("Name", typeof(string));
        table.Rows.Add(null, "a");
        table.Rows.Add(50, "a");
        table.Rows.Add(10, "a");
        table.Rows.Add(21, "b");
        table.Rows.Add(100, "b");
        return table;
    }
    static void Main(string[] args)
    {
        DataTable dt =GetTable();
        var str = dt.Compute("Sum(Value)", "Name='a' and Value is not null");
    }

You can't Call Sum with Convert, but you can try below 

DataTable dt =GetTable();
dt.Columns.Add("temp", typeof(int), "Convert(Value, 'System.Int32')");
var str = dt.Compute("Sum(temp)", "Name='a' and Value is not null");
Damith
  • 62,401
  • 13
  • 102
  • 153
  • Still getting same cast error. All the values in table are int type. – Xavier Dennis Jun 06 '13 at 07:01
  • @XavierDennis check my updated answer, it is working fine with Compute and no errors. – Damith Jun 06 '13 at 07:18
  • But problem is, i have not specified the data type of the column. Datatable is filled from an sql query. What ever the column returning from sql query will be populated in datatable. – Xavier Dennis Jun 06 '13 at 08:04
  • can you update the question with how you get data from database and what is the column type of `Value` in db? – Damith Jun 06 '13 at 08:09
0

try

dttest.Compute("Sum(Convert([Value], 'System.Int32'))","[Value] IS NOT NULL");
kostas ch.
  • 1,960
  • 1
  • 17
  • 30
0

When the program try it sum a datatable column which declared as string this error occur.
Use the below code to make compute operation on string column.

dtTable.Compute("Sum(Convert(" + col_Name + ", 'System.Int32')","");
Balan
  • 421
  • 6
  • 12
  • Depending on how you populate your DataTable, you should just be able to change the database column type to int, decimal, etc. I was getting this error and I had inadvertently created the column as a varchar(). Changed it to an int() and the error went away. Yes, not every single person has access to make changes on the db, but if you do, this is probably better than converting a string just to sum it. – user1544428 Jul 04 '21 at 15:02