Question How to Group and Sum datatable

lulu

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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:

LINEDURATION
A100:05:20
A100:07:00
A100:02:10
A100:01:50
A200:02:01
A200:03:45












I want the output to be like this:
LINEDURATION
A100:14:30
A200: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:


VB.NET:
 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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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:

VB.NET:
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:
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
So I have to use the DICTIONARY method?

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

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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:

VB.NET:
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
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
 
Last edited:

lulu

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
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.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,481
Location
Sydney, Australia
Programming Experience
10+
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.

No, the first step is not to loop through the rows and add the data to the Dictionary. If you were to read what I posted you would see that the first step, within the loop, is to check whether the key, i.e. the LINE value, is already in the Dictionary. You then do one of two different things, depending on whether it is or it isn't.

You are ignoring the fact that programming does not exist in a vacuum. The code is just an implementation of a solution to the problem. It's not the solution itself. Think about how you would do this if you were doing it with a pen and paper. If you were creating a list of each unique LINE and the sum of DURATIONs on paper, would the first thing you did be go through all the data and add every rows data to your list? Of course it wouldn't, so why would it be here? If you don't know what the code is supposed to do then how can you possibly write code to do it? Work out what the code has to do first, before trying to write it. If you don't have a set of steps that you could follow using a pen and paper to solve your problem then you shouldn't be even thinking about writing code.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
Just as a personal exercise, I tried doing exactly what you wanted with a SQL table containing 2 record, A1 - 01:05:05 and A1 - 01:54:55, to test the overflow algorithm, and I thought it would be foolish not to give you the answer... You will still have to work it into proper oracle form (although I expect it should fit in almost the same), and work it into your existing query. The output is A1 - 03:00:00. I guess in the end the preferred solution (either through database query or through local code, in VB) will be determined by where you want the overhead of all the converting to take place, on the user's computer or on the server. If there are thousands of records and/or the intended platform is a low-memory or low-bandwidth/slow portable device, doing it on the server through the query will probably be better. However if the server is small and/or the load is high, you might want the formatting to be done client-side.

SELECT T.MyName,
       RIGHT('00' + CONVERT(VARCHAR, SUM(T.Hours)+((SUM(T.Minutes)+(SUM(T.Seconds)/60))/60)), 2) + ':' +
       RIGHT('00' + CONVERT(VARCHAR, (SUM(T.Minutes)+(SUM(T.Seconds)/60))%60), 2) + ':' + 
       RIGHT('00' + CONVERT(VARCHAR, SUM(T.Seconds)%60), 2) AS Duration
FROM ( SELECT MyName,
             CONVERT(INT, SUBSTRING(MyDuration, 1, 2)) AS Hours,
             CONVERT(INT, SUBSTRING(MyDuration, 4, 2)) AS Minutes,
             CONVERT(INT, SUBSTRING(MyDuration, 7, 2)) AS Seconds
       FROM dbo.Table1 ) T
GROUP BY T.MyName
 
Last edited:

lulu

Member
Joined
Sep 25, 2012
Messages
10
Programming Experience
Beginner
Tanx jmcilhinney for your advice. And tanx Herman for your suggestion.

I study the code again through all night, tested all possible solution and got it solved.

Finally, i used LINQ to perform the group and summation and it works. Tanx all for your support :)
 
Top Bottom