CODEWITHSUNDEEP
vb.netdatatable
jonas
jonas

Reputation: 13

How can I build sums of values from DataTables in vb.net?

I'm using VB.Net to retrieve data from a database, do some calculations, and then show this data in a datagridview. I already imported the data from the database into a DataTable in my software. So far so good.

Now I want to add up values from columns where another column in the same row has a specific text.

Here's an example. I have data (in the DataTable) in the following form:

Date         //   type       //   value
10/09/2020   //   value80    //   92
10/09/2020   //   value71    //   5
10/09/2020   //   value63    //   2
10/07/2020   //   value80    //   85
10/07/2020   //   value71    //   10
10/07/2020   //   value63    //   1

I want to build the sum of the 3 type values, where "value80", "value71" and "value63" are from the same date. The new variable value_sum should be calculated such that for 10/09/2020:

value_sum = value80 + value71 + value63 = 92 + 5 + 2 = 99

and for 10/7/2020:

value_sum = value80 + value71 + value63 = 85+ 10 + 1 = 96

How can I calculate the new values in order to save them in another DataTable?

Upvotes: 1

Views: 663

Answers (1)

Mary
Mary

Reputation: 15091

In the Linq query we Group By the date field and select the date and the sum. See https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause There is a .CopyToDataTable method (https://learn.microsoft.com/en-us/dotnet/api/system.data.datatableextensions.copytodatatable?view=netcore-3.1) but I don't know if or how it works with anonymous types. It is easy enough to just loop through the query result and add the rows to the new DataTable.

Private Sub OPCode()
    'Create original DataTable with your sample data
    Dim dt As New DataTable
    dt.Columns.Add("ValueDate", GetType(Date))
    dt.Columns.Add("Type", GetType(String))
    dt.Columns.Add("Value", GetType(Integer))
    With dt.Rows
        .Add(#10/09/2020#, "value80", 92)
        .Add(#10/08/2020#, "value71", 5)
        .Add(#10/09/2020#, "value85", 2)
        .Add(#10/07/2020#, "value80", 85)
        .Add(#10/07/2020#, "value71", 10)
        .Add(#10/07/2020#, "value63", 1)
    End With
    'Select the date groups and sum by group
    Dim TotalsByDate = From row In dt.AsEnumerable
                       Group By DateValue = row.Field(Of Date)("ValueDate")
                           Into Dates = Group, Sum(row.Field(Of Integer)("Value"))
                       Select DateValue, Sum
    'Prepare a new DataTable
    Dim dt2 As New DataTable
    dt2.Columns.Add("DateGroup", GetType(Date))
    dt2.Columns.Add("DateSum", GetType(Integer))
    'Fill the new DataTable
    For Each item In TotalsByDate
        dt2.Rows.Add(item.DateValue, item.Sum)
    Next
    'Display results
    DataGridView1.DataSource = dt2
End Sub

Upvotes: 2

Related Questions