How to group and sum datatable

[lulu79]

Hi, i'm a newbie and get stuck with this problem for a few days.

I have datatable with two column, line and duration.
I want to sum the duration based on the line group. For example:

LINE DURATION
A1 00:05:20
A1 00:07:00
A1 00:02:10
A1 00:01:50
A2 00:02:01
A2 00:03:45

I want the output to be like this:

LINE DURATION
A1 00:14:30
A2 00:05:46

I cant do this straight from Oracle query because column duration is stored as Char in the database.
So i hava to convert the column to timespan and store in another column in the datatable. Here is the code:

Imports System.Data
Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
Imports Oracle.DataAccess.Types

Partial Public Class WebForm3

    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim li As ListItem


        For Each li In DDL1.Items()
            If li.Selected = True Then
                If Len(Trim(strLine)) <> 0 Then
                    strLine = strLine & "','" & li.Text
                Else
                    strLine = li.Text
                End If
            End If
        Next


    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim oradb As String = "Data Source=XXXXX;User Id=XXXXX;Password=XXXXX;"
        Dim conn As New OracleConnection(oradb)
        Dim cmd As New OracleCommand
        Dim myDataAdapter As New OracleDataAdapter
        Dim mySelect As String


        Try

            conn.Open()

            mySelect = "SELECT REFLINKTBL.LINENAME, JSFACTOR.EVENTTM  "
            mySelect = mySelect & " FROM (SELECT JSLINE.LINENAME, JSMC.RECID, JSMC.MCNAME, JSMC.GOH "
            mySelect = mySelect & " FROM JSLINE RIGHT OUTER JOIN  "
            mySelect = mySelect & " JSMC ON JSLINE.RECID = JSMC.ID_JSLINE) REFLINKTBL INNER JOIN  "
            mySelect = mySelect & " JSFACTOR ON JSFACTOR.ID_JSMC = REFLINKTBL.RECID  "
            mySelect = mySelect & " WHERE JSFACTOR.YOUIN LIKE '%CSTTRB%'  "
            mySelect = mySelect & " AND (JSFACTOR.EVENTST BETWEEN '2012/08/06 00:00:00' AND '2012/08/12 00:00:00') "
            mySelect = mySelect & " AND LINENAME IN ('" & strLine & "')  "
            mySelect = mySelect & " ORDER BY LINENAME, JSFACTOR.EVENTST "

            myDataAdapter = New OracleDataAdapter(mySelect, conn)


            Dim Ds As New DataSet()

            myDataAdapter.Fill(Ds)


            Dim dc As DataColumn

            dc = New DataColumn("DURATION", GetType(TimeSpan))

            Ds.Tables(0).Columns.Add(dc)


            Dim linename As DataColumn = Ds.Tables(0).Columns(0)
            Dim duration As DataColumn = Ds.Tables(0).Columns(2)


            For i As Integer = 0 To Ds.Tables(0).Rows.Count - 1
                Ds.Tables(0).Rows(i).Item(duration) = TimeSpan.Parse(Ds.Tables(0).Rows(i).Item("EVENTTM"))
            Next


            Ds.Tables(0).Columns.Remove("EVENTTM")



            GridView1.DataSource = Ds
            GridView1.DataBind()


        Catch ex As OracleException

            MsgBox("Error: " & ex.ToString())

        End Try



    End Sub

the problem now, i get stuck with how to group the line and get the sum of duration for each line.
Can anyone help me pls...

[TnTinMN]

Performing computions on TimeSpan and DateTime types are two areas that the DataTable class does not support very well.

That said, you are left to process the rows yourself in code.

Here are two options, both of which entail creating an array of resultant rows that can then be added to a new DataTable

1. You could iterate through the table and create a list of unique LINENAME's and then do a DT.Rows.Select to pull those rows. You would then need to iterate through the returned rows and perform the summation. Then this result would be converted into a new DataRow and stored in an Array of DataRows.

   Not difficult to do and it is easy to follow the code logic.

2. The second option essentially does the above, but uses a LINQ query on the DataTable to group and compute. These can be compact statements, but are confusing to those who do not use them very often. This will return an IENumerable(Of DataRow) that can then be iterated through and added to a DataTable.

I am going to show the LINQ method.

For your particular problem, we need to deal with the TimeSpan structure. To get a value that can be summed, I use the "Ticks" property and sum that value.

The method shown below handles the conversion the string TimeSpan value directly. No need for the TimeSpan conversion sterp that you showed.

As I do not do webpages, I have written and tested this in a WinForm project. You may need to add a reference to "Sysytem.Data.DateExtensions".

   'Create a new Table to hold the results
   Dim grpDT As DataTable = DS.Tables(0).Clone 'Copy structure of original Table
   'Make the changes you indicated to the Table structure.  You can do this as long as the table is empty.
   grpDT.Columns("EVENTTM").DataType = GetType(TimeSpan)
   grpDT.Columns("EVENTTM").ColumnName = "DURATION"

   'Now the LINQ
   'This LINQ use a helper function (DTRow) to create the DataRow
   Dim group As IEnumerable(Of DataRow) = _
      From row In DS.Tables(0).AsEnumerable() _
      Group row By LINENAME = row.Field(Of String)("LINENAME") Into grp = Group _
      Order By LINENAME _
      Select DTRow(grpDT, _
                   New Object() {LINENAME, _
                                 New TimeSpan( _
                                              grp.Sum(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks _
                                                      ) _
                                             ) _
                                } _
                  )

   'Add the query results to the new Table
   For Each row As DataRow In group
      grpDT.Rows.Add(row)
   Next

   'Assign new Table as DataSource of DGV
   dgv1.DataSource = grpDT

The helper function:

Private Function DTRow(ByVal Table As DataTable, ByVal fields() As Object) As DataRow
   Dim r As DataRow = Table.NewRow
   For i As Int32 = 0 To fields.GetUpperBound(0)
      r(i) = fields(i)
   Next
   'Note:  Adding row to Table does not work with this function called by LINQ
   Return r
End Function

[lulu79]

Tanx TnTinMN!
It works like a charm!
tanx a lot!! :)