467,894 Members | 1,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

Aggregate functions with GROUP BY Clauses on DataTable?

Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values:

Date Amount Int Balance
1/1/2004 5000.00 50.00 5050.00
1/1/2004 4000.00 40.00 4040.00
1/2/2004 1000.00 10.00 1010.00
1/2/2204 2000.00 20.00 2020.00
1/3/2004 1500.00 15.00 1515.00

I want my resultant DataTable to show in DataGrid as

Date Amount Int Balance
1/1/2004 9000.00 90.00 9090.00
1/2/2004 3000.00 30.00 3030.00
1/3/2004 1500.00 15.00 1515.00

Thanx
Nov 22 '05 #1
  • viewed: 7741
Share:
1 Reply
Job Lot,
I find the easist way is to manually create an Aggregate table.

I created the following a while ago.

Which you might be able to modify for your needs...

Option Strict On
Option Explicit On

Public Class Grouping

Private ReadOnly ds As DataSet
Private ReadOnly g1, g2, g3, d As DataTable

Public Sub New()
Dim c1, c2, c3, c4 As DataColumn
ds = New DataSet("Grouping")

' define the group 1 table
g1 = ds.Tables.Add("g1")
c1 = g1.Columns.Add("c1", GetType(Integer))
g1.PrimaryKey = New DataColumn() {c1}

' define the group 2 table
g2 = ds.Tables.Add("g2")
c1 = g2.Columns.Add("c1", GetType(Integer))
c2 = g2.Columns.Add("c2", GetType(Integer))
g2.PrimaryKey = New DataColumn() {c1, c2}

' define the group 3 table
g3 = ds.Tables.Add("g3")
c1 = g3.Columns.Add("c1", GetType(Integer))
c2 = g3.Columns.Add("c2", GetType(Integer))
c3 = g3.Columns.Add("c3", GetType(Integer))
g3.PrimaryKey = New DataColumn() {c1, c2, c3}

' define the data table
d = ds.Tables.Add("d")
c1 = d.Columns.Add("c1", GetType(Integer))
c2 = d.Columns.Add("c2", GetType(Integer))
c3 = d.Columns.Add("c3", GetType(Integer))
c4 = d.Columns.Add("c4", GetType(Integer))

End Sub

Public Sub Populate(ByVal count As Integer, ByVal maxValue As Integer)
Dim rand As New Random
Dim c1, c2, c3, c4 As Integer
For index As Integer = 1 To count
c1 = rand.Next(1, maxValue)
c2 = rand.Next(1, maxValue)
c3 = rand.Next(1, maxValue)
c4 = rand.Next(1, maxValue)
d.Rows.Add(New Object() {c1, c2, c3, c4})
Next
End Sub

Public Sub GroupBy()
For Each row As DataRow In d.Rows
AddGroup(g1, row!c1)
AddGroup(g2, row!c1, row!c2)
AddGroup(g3, row!c1, row!c2, row!c3)
Next
ds.Relations.Add(New DataColumn() {g1.Columns("c1")}, New
DataColumn() {d.Columns("c1")})
g1.Columns.Add("t1", GetType(Integer), "sum(child.c4)")
g1.Columns.Add("t2", GetType(Integer), "avg(child.c4)")
g1.Columns.Add("t3", GetType(Integer), "min(child.c4)")
g1.Columns.Add("t4", GetType(Integer), "max(child.c4)")
g1.Columns.Add("t5", GetType(Integer), "count(child.c4)")
g1.Columns.Add("t6", GetType(Integer), "stdev(child.c4)")
g1.Columns.Add("t7", GetType(Integer), "var(child.c4)")
End Sub

Private Sub AddGroup(ByVal group As DataTable, ByVal ParamArray keys()
As Object)
If group.Rows.Find(keys) Is Nothing Then
group.Rows.Add(keys)
End If
End Sub

Public Sub Save(ByVal fileName As String)
ds.WriteXml(fileName)
End Sub

Public Sub Print()
For Each row As DataRow In g1.Rows
Debug.WriteLine(row!c1.ToString())
Debug.Indent()
Debug.WriteLine(row!t1, "sum")
Debug.WriteLine(row!t2, "avg")
Debug.WriteLine(row!t3, "min")
Debug.WriteLine(row!t4, "max")
Debug.WriteLine(row!t5, "count")
Debug.WriteLine(row!t6, "stdev")
Debug.WriteLine(row!t7, "var")
Debug.Unindent()
Next
End Sub

Public Shared Sub Main()
Dim luke As New Grouping
luke.Populate(10000, 16)
luke.GroupBy()
luke.Save("Grouping.xml")
luke.Print()
End Sub

End Class

By defining the relationships between g1, g2, g3 & the d table, you can use
GetChildRows to get the rows associated with a specific group. Or use the
"child" expression syntax to get aggregate amounts... I show child
aggregates...

Hope this helps
Jay
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:EE**********************************@microsof t.com...
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values:
Date Amount Int Balance
1/1/2004 5000.00 50.00 5050.00
1/1/2004 4000.00 40.00 4040.00
1/2/2004 1000.00 10.00 1010.00
1/2/2204 2000.00 20.00 2020.00
1/3/2004 1500.00 15.00 1515.00

I want my resultant DataTable to show in DataGrid as

Date Amount Int Balance
1/1/2004 9000.00 90.00 9090.00
1/2/2004 3000.00 30.00 3030.00
1/3/2004 1500.00 15.00 1515.00

Thanx

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Marcel Hug | last post: by
6 posts views Thread by troy_lee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.