Question Dataset average

[zenny4]

Only a newbie....
I have created a new dataset and filled with student marks like the following:

Name Subject Score
Mark History 90
Mark Maths 87
John History 85
John Maths 65

And once completed with thid first dataset would then like to use this dataset to create an average for each and input this back into the 1st dataset
Name Subject Score ScrAvg
Mark History 90 88.5
Mark Maths 87 88.5
John History 85 75
John Maths 65 75

So really question is how do you create a dataset from a dataset and populate it back?

Any help greatly appreciated...

 

[jmcilhinney]

Why do you need a second DataSet? Why can't you just calculate the average and set the appropriate fields in the existing DataSet?

Also, it's the DataTable you care about, not the DataSet.

 

[zenny4]

Well what I was trying to do was what I would of done in MS Access. I would get the table with the scores. Make a new table using 'group by' NAem and Avg(Score) and then update this back to the original score table linking name.
the score is input into the dataset so I cannot average it first when i fill it.
Unless maybe i just use it and create a for each loop to average....

 

[jmcilhinney]

You certainly wouldn't create another DataSet for that. If you were using .NET 3.5 or later then you could simplify things with LINQ. In prior versions, I'd use something like this:

Dim averageScores As New Dictionary(Of String, Double)

For Each row As DataRow In myDataTable.Rows
    Dim name As String = CStr(row("Name"))

    If Not averageScores.ContainsKey(name) Then
        averageScores.Add(name, _
                          CDbl(myDataTable.Compute("AVG(Score)", _
                                                   String.Format("Name = '{0}'", _
                                                                 name))))
    End If

    row("ScrAvg") = averageScores(name)
Next

 

[zenny4]

Thanks
This worked a charm.
Most appreciated.

 

[zenny4]

Can I exclude nulls?

 

[jmcilhinney]

The second parameter of the Compute method is the filter. You can add or replace conditions in that string to filter as required.

 

[ss7thirty]

You can also use SQL functions to do this grouping by name using the AVG() aggregate function.

SELECT Name, Subject, AVG(Score) FROM YourTable GROUP BY Name, Subject

 

[cjard]

You could also add another table to your dataset, containing Name and AvgScore columns, add a datarelation to relate this new table as the parent, and your existing table as the child. Set the Expression property of the AvgScore column to be "AVG(Child.Score)"