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 said:
Is it possible to use Aggregate functions with GROUP BY Clauses on
DataTable. I have a DataTable with following values:
