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:

 

[lulu]

Tanx Herman!

but since i'm at home right now, i'll try as your suggested tomorrow.
Tanx again!

 

[jmcilhinney]

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]

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

 

[lulu]

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

 

[Icone]

You can use the SUM() function is select query for that problem.