0
DataTable dtStock = new DataTable();
DataColumn dcTotalCases = new DataColumn("TotalCases");
dcTotalCases.DataType = System.Type.GetType("System.Int32");
dcTotalCases.ReadOnly = false;
dtStock.Columns.Add(dcTotalCases);

Method 1

int sum = Convert.ToInt32(dtStock.Compute("Sum([TotalCases]) ", "[TotalCases] <> ''"));

When I use this method, I get an error

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

Method 2

int sum = Convert.ToInt32(dtStock.Compute("SUM(Convert([TotalCases], 'System.Int32'))", "");

When I use this method, I get an error

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

Please help me...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joby Kurian
  • 3,699
  • 4
  • 30
  • 38
  • Possible duplicate of [Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier](http://stackoverflow.com/questions/5637355/syntax-error-in-aggregate-argument-expecting-a-single-column-argument-with-poss) – Stephan Schielke Jan 21 '16 at 15:44

2 Answers2

6

I believe method #1 should work - just don't compare your INT column to an empty string!!

So use something like this:

int sum = Convert.ToInt32(dtStock.Compute("Sum(TotalCases)", "[TotalCases] > 0"));

or 

int sum = Convert.ToInt32(dtStock.Compute("Sum(TotalCases)", "[TotalCases] IS NOT NULL"));

or whatever you need - just don't use [TotalCases] <> '' since that compares an INT to an empty string - and that's not going to work!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • First option does not work Compute take two arguments.So i tried this way "int sum = Convert.ToInt32(dtStock.Compute("Sum([TotalCases]) ",""));".Still getting same error..Invalid usage of aggregate function Sum() and Type: String. – Joby Kurian Dec 29 '11 at 06:49
  • When i am trying this option "sum = Convert.ToInt32(dtStock.Compute("Sum([TotalCases]) ", "[TotalCases] > 0"));" i getting this error..."Cannot perform '>' operation on System.String and System.Int32." – Joby Kurian Dec 29 '11 at 06:52
  • When i am trying second option you provided,now this error "Invalid usage of aggregate function Sum() and Type: String." – Joby Kurian Dec 29 '11 at 06:54
  • @JobyKurian: you had an extra space after the `Sum(TotalCases)` in your sample - remove that unnecessary space, and both my lines of code will work just fine. – marc_s Dec 29 '11 at 07:13
1

I have a Datatable which i have not specified datatype for any column. All the values in the column are integer type. when i user the above sum() i am getting the error. Invalid usage of aggregate function Sum() and Type: String. But when i specify the datatype for the column its working correctly.

Sam
  • 1,298
  • 6
  • 30
  • 65
Xavier Dennis
  • 39
  • 1
  • 1
  • 7