VB.NET - Need to get average of data in datatable

Using VB.NET 2013, I have a database table named 'Measurement' that contains columns of temperature, pressure and flow measurements so I have columns named like: TE01, TE02, PE01, PE02, etc. along with a column with a date/time stamp and a column named 'PHASE' . The PHASE can be either 1, 2, or 3 and I need the AVERAGE of the data from each measurement column only for PHASE 3.

qryMeas = "SELECT * FROM MEASUREMENT WHERE PHASE = 3"
daMeas = new oledbDataAdapter(qryMeas, conStr)
dtMeas = new oledbDataTable

daMeas.Fill(dtMeas)

' At this point dtMeas contains all of the data from the MEASUREMENT database table where PHASE = 3

Now what I need to know is how to get the AVERAGE for each of the measurement columns without adding them up and dividing by the row count. I think there should be a simple way to do this I just can't seem to figure it out.

Can someone provide sample code that they know works. I have looked on the Microsoft website regarding Datatable Compute Method but that talks about datasets and objects and I tried adapting their example code but couldn't get it to work.

Help please!

Thanks,
Charlie
charlieb01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Why don't you let the SQL Server do the job?
qryMeas = "SELECT AVG(TE01), AVG(TE02), AVG(PE01), AVG(PE02)
FROM MEASUREMENT 
WHERE PHASE = 3"

Open in new window

0
charlieb01Author Commented:
Vitor,
The problem with the code you suggest is that my code won't always know the names of the columns and there could be as many as 300 columns of sensor readings which would make for a very long query.
0
Rose BabuSenior Team ManagerCommented:
try the below code
Dim avgVal As Integer = -1
Dim strFieldName As String = String.Empty

strFieldName = "TE01" ' You can change it dynamically with the valid column name
avgVal = dtTable.Compute("Avg([" & strFieldName & "])", "")

Open in new window

Try this and let us know...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
charlieb01Author Commented:
Perfect, this is exactly what I needed. Tested it and it works perfectly.

Thanks,
Charlie
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.