Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-15
4
959 Views
Last Modified: 2014-10-15
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
0
Comment
Question by:charlieb01
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40381847
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
 

Author Comment

by:charlieb01
ID: 40381860
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
 
LVL 16

Accepted Solution

by:
Rose Babu earned 500 total points
ID: 40382062
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
 

Author Closing Comment

by:charlieb01
ID: 40382669
Perfect, this is exactly what I needed. Tested it and it works perfectly.

Thanks,
Charlie
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question