DataTable.Compute() Result type

[sucharith]

I have a datatable with some columns of int data type. I have been
using the datatable.compute() method to calculate the average of a
particular columns for all rows. But the result given is of type int
and not float. In essence it is reportingthe avg of 2 and 3 as 2 and
not 2.5.

If I convert the type definition of the column to double it works
fine.

Is there any method to make it return double type values without
changing teh data types. I have tried using Convert() but it Think I
am messing up somewhere.

Dim dt As New Dataset1.TestDataDataTable
Dim dr As Dataset1.TestDataRow
Dim x

For x = 1 To 10
dr = dt.NewTestDataRow
dr.Name = " Name" & x
dr.Age = x
dt.AddTestDataRow(dr)
Next
Debug.WriteLine("Average Age=" & dt.Compute("AVG(Age)", ""))


Age is defined as int. Teh correct avg for this code is 5.5 but it
reports 5.


Thank in advance.
Sucharith

 

[William Ryan eMVP]

Sucharith:

The return type for Compute is Type Object. and can be casted. However, the
trouble is probably b/c Age is an Integer.

What does your Convert Statement look like? Remember it should look like
Convert(Age, 'System.Single')

 

[Sucharith Vanguri]

This is what I used.
dt.compute("AVG(Convert(Age,'System.Double'))","")

and I am getting the following error
Unhandled Exception: System.Data.SyntaxErrorException: Syntax error in
aggregate argument: Expecting a single column argument with possible
'Child' qualifier.
at System.Data.ExpressionParser.ParseAggregateArgument(FunctionId
aggregate)
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression..ctor(String expression, DataTable
table, Type type)
at System.Data.DataExpression..ctor(String expression, DataTable
table)
at System.Data.DataTable.Compute(String expression, String filter)

I have also tried the following but, it gives me an integer value only.
Convert.ToDouble(dt.Compute("AVG(Age)", ""))


Sucharith

 

[Jay B. Harlow [MVP - Outlook]]

Sucharith,
Have you considered adding a 'computed' column to your data set that
contains the expression "Convert(Age,'System.Double')", then doing the avg
on this computed column?

dt.Columns.Add("Agef", GetType(Double), "Convert(Age,'System.Double')")

dt.compute("AVG(Agef)","")

Hope this helps
Jay

 

[Sucharith]

Thanks Jay,

I have given it a thought, but the problem is that there are more than
10 columns that are declared Type INT. instead of creating new columns
of double, I think it would be advisable to change the Type to double
instead.

That is a simple fix for my problem, but I would like to know if the AVG
can work properly in .net for my future work.

the AVG works properly in SQL but not in .net through teh
DataTable.Compute method. I need some fix for it to be accessible
correctly through .net

Thanks again.
Sucharith Vanguri

 

[William Ryan eMVP]

Sucharith:

I was out of town, sorry for not replying sooner. Let me take a look at it
and I'll see what I can come up with. My previous post should work with an
Expression column, but I realized it's blowing up in Compute like you
mention. I'll get back to you shortly.

Cheers,

Bill

 

[Jay B. Harlow [MVP - Outlook]]

Sucharith,

I have given it a thought, but the problem is that there are more than
10 columns that are declared Type INT. instead of creating new columns
of double, I think it would be advisable to change the Type to double
instead.

Agreed changing all 10 to double would be easier, at least short term.
However that may introduce other problems...

Another option would be to create your own Compute rather then relying on
the DataTable.Compute. Especially if you are computing on mutliple columns
(you could do all columns in one pass, instead of multiple passes).

I cannot really comment on future changes to data sets...

We'll have to see what William comes up with.

Hope this helps
Jay