How do I 'GROUP' from a Dataset to create a new Dataset of the Group 2

[Kieran777]

Hi
In Visual Basic does anyone have details on grouping data from a dataset or dataview to another dataset or even a dataview.

ie. As an example only - I have data as such:

Pink, 10
Green, 20
Pink, 1
Red, 12
Red, 1
Blue, 50
Pink, 1

in a dataset (7 records). What I want to do is have a simple way of Grouping these records so I can create a dataset from it as such

i.e.,
Colour, Count, Sum
Pink, 3, 12
Green, 1, 20
Red, 2, 13
Blue, 1, 50

I know I can go back to the database and issue an SQL statement to get this bit I need to be able to do it on the dataset that was already retrieved from the database.

regards
Kieran777

 

[blounty]

Hi,

I put this together really quickly so there is propbably a better way of doing this but it may help.

basically fill your dataset. create a dataview based on the table required. filter your dataview. create a new dataset and table. pass the dataview to the table. add the table to the dataset.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ta As New DataSet1TableAdapters.AccountsTableAdapter
        Dim ds As New DataSet1
        ta.Fill(ds.Accounts)

        Dim dv As New DataView(ds.Accounts)
        dv.RowFilter = "Name = 'Blounty Enterprises'"

        Dim newds As New DataSet
        Dim dt As New DataTable
        dt = dv.ToTable
        newds.Tables.Add(dt)
        DataGridView1.DataSource = newds.Tables(0)

    End Sub

Hope this helps.

Alex

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
Rick Cook, The Wizardry Compiled

 

[jebenson]

Well, the .RowFilter method of a DataView (or the .Select method of a DataTable) will get you part of the way there. With these methods you can filter the data, but you can't get what you want (a count of each color and a sum of the numbers with each color) using just .RowFilter or .Select. Basically these methods are like the WHERE clause in a SQL statement.

What you can do is filter a DataView on each color, use the DataView's .Count property to get the number of records for each color, then loop through the filtered DataView to sum the amounts for each color.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[jebenson]

I did a little more research on this, and found that the DataTable's .Coumpte method can simplify this. Here's an example that will get the Count and Sum for the color Red:

'DataTable is named dt

Dim ColorCount As Integer
Dim ColorQuantity As Integer

ColorCount = dt.Compute("Count(Color)", "Color='Red'")
ColorQuantity = dt.Compute("Sum(Quantity)", "Color='Red'")

I'll leave it to you how to get this into another table.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!