[vennesschan]

Hi,

Is somebody can help me with the following question?

How can I get the AVERAGE number from the column of ListView1.Items(ListView1.Items.Count - 1).SubItems.Add((reader("High").ToString) - (reader("Low").ToString)) and shows the AVERAGE number on the label.

Dim Cmd As SqlCommand = New SqlCommand(SelectCmd, conn)
Dim reader As SqlDataReader
reader = Cmd.ExecuteReader()

'**************************
Do While (reader.Read())

ListView1.Items.Add(reader("Id").ToString)
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reade("Date").ToString)
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reade("High").ToString)
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reader("Low").ToString)
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add((reader("High").ToString) - (reader("Low").ToString)) 
Loop
'***************************
reader.Close()
conn.Close()

For example:
The column outputs will be:
1
3
5
2
8
________
Avg: 3.8

That will be the number i would like to have it on the label.

Thank you for your help!

Admin Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

[CharlieMay]

You could create a function to iterate through the listview and sum the total / count.

Here's an example of a function that will do it.

    Private Function GetAvg(ByVal lv As ListView, ByVal col As Integer) As Double
        Dim total As Double
             For Each itm As ListViewItem In lv.Items
            total += CDbl(itm.SubItems(col).Text)
        Next
        GetAvg = total / lv.Items.Count
      
    End Function

Lets say you have column 2 holding the values you want to average in ListView1

dim result as double
result = GetAvg(ListView1,2)

Edited to Double instead of Integer

This post has been edited by CharlieMay: 20 April 2010 - 08:04 AM

[vennesschan]

CharlieMay, on 20 April 2010 - 06:58 AM, said:

You could create a function to iterate through the listview and sum the total / count.

Here's an example of a function that will do it.

    Private Function GetAvg(ByVal lv As ListView, ByVal col As Integer) As Double
        Dim total As Double
             For Each itm As ListViewItem In lv.Items
            total += CDbl(itm.SubItems(col).Text)
        Next
        GetAvg = total / lv.Items.Count
      
    End Function

Lets say you have column 2 holding the values you want to average in ListView1

dim result as double
result = GetAvg(ListView1,2)

Edited to Double instead of Integer

Thank you.

[vennesschan]

Hi Charlie,

Let say this function has got us the average number 3.5 from the lv column 2. Is there any method that i can also get the above average and below average from the lv coloumn 2?

Example:
(1+2+3+4+5+6) / 6
Avg: 3.5

Since the GetAvg function has already got us the average number 3.5, now can i have the app. calculate the below and above average to show on the labels?
Example:
(1+2+3) / 3
Below Average: 2

(4+5+6) / 3
Above Average: 5

[CharlieMay]

Well, you could use similar code to the get each value and compare it against the average

Create a Function that takes the calculated average as a parameter and then iterate and compare against it and return the result. I intentionally left out part of an important step but you should be able to figure out what that is (see comment in code window)

Then you can either create another function FindHighAvg and fix the code accordingly or you could add a second parameter to denote that you are looking for High or Low and then compare based on the value of that parameter to return your result.

EDIT:
Here is an example of finding the average of the "Below Avg" values

Private Function FindLowAvg(ByVal avg as Double) As Double
dim Count as integer =0
dim total as double
For Each itm as Listviewitem In ListView1.Items
  If CDbl(itm.subitems(1).text) 'OK, this line isn't finished, Do you know what goes here?
   count +=1
   total += CDbl(itm.subitems(1).text)
  end if
Next
FindLowAvg = total / count
End Function

You will also need to adjust the subitem indexes accordingly.

This post has been edited by CharlieMay: 23 April 2010 - 06:49 AM

[vennesschan]

I am pretty new to vb.net but i wish i can answer it correctly.

Since we didn't tell the function where should we locate the mid point which means the Avg. Should I put < avg on this line.

[CharlieMay]

yep

[vennesschan]

            Dim ID As String
            ID = Inputtxt.Text 	    

            Dim conn As SqlConnection
            conn = New SqlConnection(Source)

            conn.Open()

                ListView1.Clear()
                ListView1.View = View.Details
                ListView1.Columns.Add("Id", 60, HorizontalAlignment.Left)
                ListView1.Columns.Add("Date", 150, HorizontalAlignment.Left)
                ListView1.Columns.Add("High", 80, HorizontalAlignment.Left)
                ListView1.Columns.Add("Low ", 80, HorizontalAlignment.Left)
                ListView1.Columns.Add("High - Low", 150, HorizontalAlignment.Left)

            Dim SelectCmd As String
            SelectCmd = "Select Distinct Top 120 Id, Date, High, Low from Report where Symbol ='" & ID & "'" & "Order By Date DESC"

            Dim Cmd As SqlCommand = New SqlCommand(SelectCmd, conn)
            Dim reader As SqlDataReader
            reader = Cmd.ExecuteReader()

            Do While (reader.Read())
                ListView1.Items.Add(reader("Id").ToString)
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reader("Date").ToString)
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reader("High").ToString)
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(reader("Low").ToString)
 		ListView1.Items(ListView1.Items.Count - 1).SubItems.Add((reader("High").ToString) - (reader("Low").ToString))
            Loop
           
            reader.Close()
            conn.Close()

	     Dim result As Double
             result = GetAvg(ListView1, 4)
             LabelAvg.Text = Math.Round(result, 3)

 	     Dim l As Double
             l = FindLowAvg(result)
             LavgLabel.Text = Math.Round(l, 3)

             Dim h As Double
             h = FindHighAvg(result)
             HavgLabel.Text = Math.Round(h, 3)

Since I got all the Averages now. I am trying to make another ListView which is called ListView2 to hold the Id, Average Volume, Low Average Volume and High Average Volume group by Id. Do you think I can use the similiar functions to work on the ListView2?

[CharlieMay]

Well, If I understand correctly, then instead of getting the average of all the items in the list, you're going to get it by each ID? Where multiple ID's will exist in the listview2? If so, I think your best bet would be to sort the listview so that each of the ID's are grouped and then interate through them using similar code only when the ID changes, perform the proper calculations to the the averages you want and then repeat the process through the next ID. If this isn't what you're trying to achieve, could you display an example of what listview2 would contain and what you want to do with that data?

This post has been edited by CharlieMay: 24 April 2010 - 08:47 PM

[vennesschan]

Thanks for reply.

I am currently working on the most simple way to get the Average volume and group by Id from the db as the following example code, afterall i will have it find the low average volume and the high average volume too. However I have found the code didn't go throught the Do While method when i did the debug. What did i do wrong? Did i do sth wrong on the sql query? or I cannot use Do While method? Thank you for your advise.

Dim conn As SqlConnection
conn = New SqlConnection(Source)

conn.Open()

ListView2.Clear()

ListView2.View = View.Details
ListView2.Columns.Add("Id", 60, HorizontalAlignment.Left)         
ListView2.Columns.Add("Volume", 100, HorizontalAlignment.Left)

Dim SelectCmd As String
SelectCmd = "Select Id, Avg(Volume) As AvgVolume from Report Group By Id"


Dim Cmd As SqlCommand = New SqlCommand(SelectCmd, conn)
Dim reader As SqlDataReader
reader = Cmd.ExecuteReader()

Do While (reader.Read())
ListView2.Items.Add(reader("Id").ToString)
ListView2.Items(ListView2.Items.Count - 1).SubItems.Add(reader("AvgVolume)").ToString)
Loop

reader.Close()
conn.Close()

This post has been edited by vennesschan: 25 April 2010 - 01:37 AM

[CharlieMay]

I'm not seeing any apparent problem with the code, I assume you're using the same connectionstring that you used to fill the first listivew?

[vennesschan]

Right. I am using everything as same as the the ListView1 which include the sqlconnection string, however, i found the following message from the immediate window.

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

[CharlieMay]

do you have this code inside of a Try Catch statement? if so, try remarking out the Try Catch lines to disable it and run the code again.

If not, can you post the entire form class code. I ran your code against a database on my end and it worked as expected.

[vennesschan]

Thank you for your reply!

Yes, after I took out the Try Catch Statement and found the app. got stucked on reader = cmd.ExecuteReader() when I ran it.

My project's code as below:

Private Sub GetAllDataBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetAllDataBtn.Click

'Try
Dim Source As String
Source = "Data Source=GREEN001\SQLEXPRESS ********* M Y C O N N E C T I O N ********** "

Dim conn As SqlConnection 
conn = New SqlConnection(Source) 
 
conn.Open() 
 
ListView2.Clear() 
 
ListView2.View = View.Details 
ListView2.Columns.Add("Id", 60, HorizontalAlignment.Left)          
ListView2.Columns.Add("Volume", 100, HorizontalAlignment.Left) 
 
Dim SelectCmd As String 
SelectCmd = "Select Id, Avg(Volume) As AvgVolume from Report Group By Id" 
  
Dim Cmd As SqlCommand = New SqlCommand(SelectCmd, conn) 
Dim reader As SqlDataReader 
reader = Cmd.ExecuteReader() '<----- After remarking out the Try Catch statement. The app. got stucked here :(/> 
                             'Message: SqlException was unhandled - Operand data type char is invalid for avg operator
 
Do While (reader.Read()) 
ListView2.Items.Add(reader("Id").ToString) 
ListView2.Items(ListView2.Items.Count - 1).SubItems.Add(reader("AvgVolume)").ToString) 
Loop

reader.Close()
conn.Close()

'Catch ex As Exception
'End Try
End Sub

This post has been edited by vennesschan: 26 April 2010 - 02:25 AM

[CharlieMay]

Ok, it is telling you that you have Volume defined as a non-numeric datatype in your database and avg needs a number. You can either change your field type to a numeric datatype or you could try changing the aggregate in the select statement to Avg(val(volume)) As AvgVolume.