Question How to Group and Sum datatable

[lulu]

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()
            GridView1.DataSource = Ds
            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 :apologetic:

 

[jmcilhinney]

The non-LINQ way would be to loop through the rows and add the data to a Dictionary as you go. Check whether the LINE is already in the Dictionary. If it is, get the current value, add the new DURATION to it and update the Dictionary. If it's not, add it with the DURATION. When you're done you can loop through the Dictionary and get the summed DURATION for each LINE.

 

[lulu]

jmcilhinney, tanx for your reply.

However, I'm not familiar with DICTIONARY and have tried doing the summation using LINQ (just learn about LINQ yesterday).

So far here is what i've done:

Dim totalSum As Datatable = Ds.Table(0)

Dim query = _
From s In totalSum.AsEnumerable() _
Let line = s.field(Of String)("LINENAME") _
Let totDur = s.field(Of Timespan)(duration) _
Group By line Into totSumD = Sum(totDur) _
Select line, [COLOR=#006400]totSumD [/COLOR]

But i get stuck on how to do the summation since the type is timespan and it keep get error of "overload resolution failed because no accessible 'SUM' can be called with these argument."

Is it not possible to do the timespan summation in LINQ?

or I have to do the DICTIONARY method? if so, how to do it? I've search about the dictionary but i'm afraid it can't sum the timespan value.

Sorry i'm still learning and hope someone can help me :adoration:

 

[jmcilhinney]

It is possible in LINQ, but I was guessing that grouping with LINQ was a bit on the advanced side for you. LINQ is great but if you can't do the things that LINQ does manually then you're not going to understand what LINQ does. What I suggested is how everyone would have done it before LINQ. If you want to use LINQ then it's important to understand what the LINQ code does. The whole point of the code is to group by line and sum the durations for each group so you're not going to want to select the individual durations at the end. You're going to want to select the summed durations, which are created by the Group By clause.

 

[lulu]

So I have to use the DICTIONARY method?

is it possible to sum the timespan datatype if i use DICTIONARY?

 

[jmcilhinney]

So I have to use the DICTIONARY method?

Given that I specifically said that it's possible using LINQ in my last post, that question is completely redundant.

is it possible to sum the timespan datatype if i use DICTIONARY?

Given that that's the whole point of this thread and I suggested using a Dictionary in the first place, that question is completely redundant too.

 

[lulu]

Now i'm trying to do the Dictionary method and get error "an item with the same key has already been added"

Here is the code:

Dim dct As New Dictionary (Of String, String)

For each r As Datarow In Ds.Tables(0).Rows
     dct.Add(r.Item(1).ToString(), r.Item(0).ToString())
Next

Another question, is it correct to declare it as a String since the data should be in a Timespan format?
Please correct me if it's wrong.
i really need to solve this problem to proceed to the next steps. :apologetic:
Thanks a lot.

 

[jmcilhinney]

It's not going to be a Dictionary(Of String, String). The LINE values will be the keys and they are Strings, but the idea is to convert the DURATION values to TimeSpans and store the sum of those TimeSpans against the appropriate key.

If you go back to post #2 you'll see that I wrote out the steps you need to perform and your code doesn't perform those steps. The one step I didn't include is the conversion of the DURATION String to a TimeSpan.

 

[lulu]

ok. So first i dont have to convert the DURATION of type Char to timespan?
I just insert straight from database to the dataset without converting it?

Is that so?

Sir, can you please show example please...

 

[jmcilhinney]

Certainly T-SQL, as used by SQL Server, could convert the text to an appropriate data type while retrieving it. Quite possibly PL/SQL, as used by Oracle, could do it too but, if so, I don't know how. If you were going to do that though, you could do the grouping and summing in the SQL in the first place. If this is a VB question then it's because you're using VB to do it, so I assume that you are retrieving the data as text and you need to convert it to TimeSpan values in VB code.

Knowing that you need to use TimeSpan values, the first thing you should do is read the documentation for the TimeSpan structure. It's highly likely that that will show you how to convert to that type from a String, don't you think? Always look in the obvious places first and, when you need help with a type, the Help for that type should be the most obvious place.

 

[lulu]

I know how to do the conversion from string to timespan type and sum it in vb.
The problem is, i got the total of summation for the whole of DURATION column. Not sum by group (LINE).
Do you have any idea how i can sum it by group?

 

[lulu]

Or maybe i should use FOR EACH selected LINE, i sum the DURATION for the line and loop it to the next LINE?

but i'm not sure how to do the loop to get each line and sum the DURATION by LINE.

 

[jmcilhinney]

If you follow the instructions I provided in post #2 then it will work. If you don't then it won't.

 

[Herman]

There is no reason you cannot do that in the query itself, you just have to separate the seconds, minutes, and hours and do some arithmetics... I do not know the specifics of an Oracle database, but in SQL it would be something like this:

SELECT LINENAME,
       SUM(Seconds)%60 As TotalSeconds,
       (SUM(Minutes)+(SUM(Seconds)/60))%60 As TotalMinutes,
       (SUM(Hours)+(SUM(Minutes)/60)) As TotalHours
FROM (SELECT REFLINKTBL.LINENAME,
             CONVERT(INT, SUBSTRING(JSFACTOR.EVENTTM, 0, 2)) AS Hours,
             CONVERT(INT, SUBSTRING(JSFACTOR.EVENTTM, 3, 2)) AS Minutes,
             CONVERT(INT, SUBSTRING(JSFACTOR.EVENTTM, 6, 2)) AS Seconds
      FROM ...)
GROUP BY LINENAME

 

[lulu]

sir, can you please show me the example on how to loop through the rows and add the data to a Dictionary?

What am i confused is, in the first step in post #2 which is to loop through the rows and add the data to a Dictionary , i should declare the type of DURATION as what? if you refer to #7 post, that is how i'm trying to loop through the rows and add it to the dictionary.