WalangAlam

How do i compute for the Average and Standard Deviation of column in a table
I have this code in VB6 but i don't know how to do it in VB.NET.


With rsMean_SD
'get the Mean
Set rs = Db.OpenRecordset("SELECT AVG(TOTAL_RS) AS Mean from tblResult")
.AddNew
!Mean = rs!Mean
.Update
'get the SD
Set rs = Db.OpenRecordset("SELECT STDEV(TOTAL_RS) AS SD from tblResult")
.MoveFirst
.Edit
!SD = Round(rs!SD, 4)
.Update
End With


Re: Visual Basic General Compute for Average and Standard Dev of a column

SoloCoder

Try this for a simple eg:

Code Snippet

Dim Conn As New SQLConnection(yourConnectionString)

Dim Comm As New SQLCommand("SELECT AVG(TOTAL_RS) AS Mean from tblResult",Conn)

Conn.Open()

Dim _avg as Decimal = Comm.ExecuteScalar()

Conn.Close()

I haven't actually tried this for Avg and Mean but it works for Count and Max etc and is useful cos you can edit the data from code, adding rows and stuff:

Get the data into a DataTable (you could use a DataAdapter to Fill a Dataset) and then use the Compute method on the Datatable, supplying the correct string (like "count(*)" etc)

Hope this helps!





Re: Visual Basic General Compute for Average and Standard Dev of a column

WalangAlam

AVG(TOTAL_RS) AS Mean returns a whole number 30 instead of decimal number 30.1145.
I need it to be precise up to the 4th decimal place. How could i do that




Re: Visual Basic General Compute for Average and Standard Dev of a column

SoloCoder

That's strange as I have just been developing an app that uses an Access 2000 database and a select statement using AVG returns a decimal. The AVG command is a function of your Database provider, not the .Net framework. Perhaps you could tell me what provider/database you use



Re: Visual Basic General Compute for Average and Standard Dev of a column

WalangAlam

I use SQL Server 2005 express. I computed the same data using excel avg and it returned a decimal value. The STDEV though returned a decimal value but the AVG returned a whole number. Im puzzled.




Re: Visual Basic General Compute for Average and Standard Dev of a column

WalangAlam

Thank you SoloCoder. Now i could continue with my projects.