Share via

Datatable find the Count of the Column item.

  • Article

Question

Thursday, July 21, 2016 10:52 AM

I have a Datatable.

Col1

Microsoft

Linux

Office

Microsoft

Windows

Microsoft

Linux

Output Required:

Col1                         Col2

Microsoft                    1

Linux                          1

Office                          1

Microsoft                     2

Windows                    1

Microsoft                     3

Linux                          2

I need the Fastest Way to Count ...Preferably SQL Query, Lambda or Linq will do..

I have Huge Data...

All replies (14)

Thursday, July 21, 2016 3:09 PM âś…Answered | 1 vote

Dim dt As DataTable = New DataTable
dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})

dt.Rows.Add("Microsoft")
dt.Rows.Add("Linux")
dt.Rows.Add("Office")
dt.Rows.Add("Microsoft")
dt.Rows.Add("Windows")
dt.Rows.Add("Microsoft")
dt.Rows.Add("Linux")



Dim dict As New Dictionary(Of String, Integer)(dt.Rows.Count)

dt.Columns.Add(New DataColumn With {.ColumnName = "Col2", .DataType = GetType(Int32)})

For indx As Integer = 0 To dt.Rows.Count - 1
    Dim key As String = dt.Rows(indx)("Col1").ToString
    Dim value As Integer
    dict(key) = If(dict.TryGetValue(key, value), value + 1, value + 1)
    dt.Rows(indx)("Col2") = value + 1
Next

gives:

Thursday, July 21, 2016 11:37 AM

Hi,

use can use this query

select  col1, count(*) over ( 
                            PARTITION BY col1 ROWS UNBOUNDED PRECEDING ) as col2 from company

it orders rows by col1

Thanks, Mahmoud

Thursday, July 21, 2016 11:38 AM

Here is a method done in Lambda

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Count", .DataType = GetType(Int32)})

dt.Rows.Add(New Object() {"Microsoft", 1})
dt.Rows.Add(New Object() {"Linux", 1})
dt.Rows.Add(New Object() {"Office", 1})
dt.Rows.Add(New Object() {"Microsoft", 2})
dt.Rows.Add(New Object() {"Windows", 1})
dt.Rows.Add(New Object() {"Microsoft", 3})
dt.Rows.Add(New Object() {"Linux", 2})

Dim ProductData = dt.AsEnumerable.GroupBy(
    Function(Item) Item.Field(Of String)("Name")) _
    .Select(Function(group) New With
    {
        Key .Name = group.Key,
        Key .Total = group.Sum(Function(x) x.Field(Of Int32)("Count")),
        Key .Students = group.OrderByDescending(Function(x) x.Field(Of Int32)("Count"))}) _
    .OrderBy(Function(group) group.Students.First.Field(Of String)("Name"))


For Each group In ProductData
    Console.WriteLine("Group: {0} Sum: {1}", group.Name, group.Total)
Next

Group: Linux Sum: 3
Group: Microsoft Sum: 6
Group: Office Sum: 1
Group: Windows Sum: 1

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Thursday, July 21, 2016 11:44 AM

Hi,

use can use this query

select  col1, count(*) over ( 
                            PARTITION BY col1 ROWS UNBOUNDED PRECEDING ) as col2 from company

it orders rows by col1

Thanks, Mahmoud

How can i Use this Query in VB.net to add 1 Column and Add Row Items

 newTable.Columns.Add("Count", GetType(Integer))

Dim kk As DataRow() = newTable.Select()

Thursday, July 21, 2016 11:48 AM

How can i Use this Query in VB.net to add 1 Column and Add Row Items

newTable.Columns.Add("Count", GetType(Integer))

Dim kk As DataRow() = newTable.Select().....Dont know how to use this...

select  col1, count(*) over ( 
                            PARTITION BY col1 ROWS UNBOUNDED PRECEDING ) as col2 from company

Thursday, July 21, 2016 11:59 AM

Here is a method done in Lambda

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Count", .DataType = GetType(Int32)})

dt.Rows.Add(New Object() {"Microsoft", 1})
dt.Rows.Add(New Object() {"Linux", 1})
dt.Rows.Add(New Object() {"Office", 1})
dt.Rows.Add(New Object() {"Microsoft", 2})
dt.Rows.Add(New Object() {"Windows", 1})
dt.Rows.Add(New Object() {"Microsoft", 3})
dt.Rows.Add(New Object() {"Linux", 2})

Dim ProductData = dt.AsEnumerable.GroupBy(
    Function(Item) Item.Field(Of String)("Name")) _
    .Select(Function(group) New With
    {
        Key .Name = group.Key,
        Key .Total = group.Sum(Function(x) x.Field(Of Int32)("Count")),
        Key .Students = group.OrderByDescending(Function(x) x.Field(Of Int32)("Count"))}) _
    .OrderBy(Function(group) group.Students.First.Field(Of String)("Name"))


For Each group In ProductData
    Console.WriteLine("Group: {0} Sum: {1}", group.Name, group.Total)
Next

Group: Linux Sum: 3
Group: Microsoft Sum: 6
Group: Office Sum: 1
Group: Windows Sum: 1

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Karen DB Null Issue.

Thursday, July 21, 2016 12:01 PM

It seems you dropped a lot of the same questions in this forum. 

Look at the answer I gave in the other thread. 

Manly, a datatable is an unsorted collection of rows which are always added add the end, therefore you cannot do in the raw way like you intent. 

Success
Cor

Thursday, July 21, 2016 12:37 PM

Count would be your Col2. The exception is because in one or more rows the Col2 value is null. Try the following

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Col2", .DataType = GetType(Int32)})

dt.Rows.Add(New Object() {"Microsoft", 1})
dt.Rows.Add(New Object() {"Linux", 1})
dt.Rows.Add(New Object() {"Office", 1})
dt.Rows.Add(New Object() {"Microsoft"})
dt.Rows.Add(New Object() {"Windows", 1})
dt.Rows.Add(New Object() {"Microsoft", 3})
dt.Rows.Add(New Object() {"Linux", 2})

Dim ProductData = dt.AsEnumerable.GroupBy(
    Function(Item) Item.Field(Of String)("Col1")) _
    .Select(Function(group) New With
    {
        Key .Name = group.Key,
        Key .Total = group.Sum(
        Function(x)
            If IsDBNull(x.Item("Col2")) Then
                Return 0
            Else
                Return x.Field(Of Int32)("Col2")
            End If
        End Function),
        Key .Row = group.OrderByDescending(
        Function(x)
            If IsDBNull(x.Item("Col2")) Then
                Return 0
            Else
                Return x.Field(Of Int32)("Col2")
            End If
        End Function)}) _
    .OrderBy(Function(group) group.Row.First.Field(Of String)("Col1"))


For Each group In ProductData
    Console.WriteLine("Group: {0} Sum: {1}", group.Name, group.Total)
Next

Group: Linux Sum: 3
Group: Microsoft Sum: 4
Group: Office Sum: 1
Group: Windows Sum: 1

The total has changed for Microsoft, it was 2, now null for one row.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Thursday, July 21, 2016 1:31 PM

Karen... : I dont need Sum I need the Count....and wanted to let u know that....I have Only 1 Column filled with data...i.e

Datatable has the Below data.

Input:

Col1             Col2

Microsoft       Blank

Linux             Blank

Office              Blank

Microsoft            Blank

Windows            Blank

Microsoft           Blank

Linux               Blank

Output Required:

Col1                         Col2

Microsoft                    1

Linux                          1

Office                          1

Microsoft                     2

Windows                    1

Microsoft                     3

Linux                          2

Thursday, July 21, 2016 1:32 PM

Count would be your Col2. The exception is because in one or more rows the Col2 value is null. Try the following

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Col2", .DataType = GetType(Int32)})

dt.Rows.Add(New Object() {"Microsoft", 1})
dt.Rows.Add(New Object() {"Linux", 1})
dt.Rows.Add(New Object() {"Office", 1})
dt.Rows.Add(New Object() {"Microsoft"})
dt.Rows.Add(New Object() {"Windows", 1})
dt.Rows.Add(New Object() {"Microsoft", 3})
dt.Rows.Add(New Object() {"Linux", 2})

Dim ProductData = dt.AsEnumerable.GroupBy(
    Function(Item) Item.Field(Of String)("Col1")) _
    .Select(Function(group) New With
    {
        Key .Name = group.Key,
        Key .Total = group.Sum(
        Function(x)
            If IsDBNull(x.Item("Col2")) Then
                Return 0
            Else
                Return x.Field(Of Int32)("Col2")
            End If
        End Function),
        Key .Row = group.OrderByDescending(
        Function(x)
            If IsDBNull(x.Item("Col2")) Then
                Return 0
            Else
                Return x.Field(Of Int32)("Col2")
            End If
        End Function)}) _
    .OrderBy(Function(group) group.Row.First.Field(Of String)("Col1"))


For Each group In ProductData
    Console.WriteLine("Group: {0} Sum: {1}", group.Name, group.Total)
Next

Group: Linux Sum: 3
Group: Microsoft Sum: 4
Group: Office Sum: 1
Group: Windows Sum: 1

The total has changed for Microsoft, it was 2, now null for one row.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Karen... : I dont need Sum I need the Count....and wanted to let u know that....I have Only 1 Column filled with data...i.e

Datatable has the Below data.

Input:

Col1             Col2

Microsoft       Blank

Linux             Blank

Office              Blank

Microsoft            Blank

Windows            Blank

Microsoft           Blank

Linux               Blank

Output Required:

Col1                         Col2

Microsoft                    1

Linux                          1

Office                          1

Microsoft                     2

Windows                    1

Microsoft                     3

Linux                          2

Thursday, July 21, 2016 2:56 PM

This give the count for distinct items.

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})

dt.Rows.Add(New Object() {"Microsoft"})
dt.Rows.Add(New Object() {"Linux"})
dt.Rows.Add(New Object() {"Office"})
dt.Rows.Add(New Object() {"Microsoft"})
dt.Rows.Add(New Object() {"Windows"})
dt.Rows.Add(New Object() {"Microsoft"})
dt.Rows.Add(New Object() {"Linux"})

Dim ProductData = dt.AsEnumerable.GroupBy(
    Function(Item) Item.Field(Of String)("Col1")) _
    .Select(Function(group) New With
    {
        Key .Name = group.Key,
        Key .Count = group.Count()}).Distinct


For Each group In ProductData
    Console.WriteLine("Group: {0} Count: {1}", group.Name, group.Count)
Next

Group: Microsoft Count: 3
Group: Linux Count: 2
Group: Office Count: 1
Group: Windows Count: 1

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Thursday, July 21, 2016 3:16 PM

Why do you prefer SQL Query, Lamda or Ling (what is the same) while there is already since framework 2.0 a simple solution. 

Module Module1
    Sub Main()
        Dim dt As New DataTable With {.TableName = "MyTable"}
        dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})
        dt.Rows.Add(New Object() {"Microsoft"})
        dt.Rows.Add(New Object() {"Linux"})
        dt.Rows.Add(New Object() {"Office"})
        dt.Rows.Add(New Object() {"Microsoft"})
        dt.Rows.Add(New Object() {"Windows"})
        dt.Rows.Add(New Object() {"Microsoft"})
        dt.Rows.Add(New Object() {"Linux"})
        Console.WriteLine(dt.DefaultView.ToTable(True, "Col1").Rows.Count)
        Console.ReadKey()
    End Sub
End Module

And the fastest one. I wrote this already in one of the other questions about this subject of you a while ago today.

I assume I could use the part of your code Karen?

Success
Cor

Thursday, July 21, 2016 3:27 PM

Dim dt As DataTable = New DataTable
dt.Columns.Add(New DataColumn With {.ColumnName = "Col1", .DataType = GetType(String)})

dt.Rows.Add("Microsoft")
dt.Rows.Add("Linux")
dt.Rows.Add("Office")
dt.Rows.Add("Microsoft")
dt.Rows.Add("Windows")
dt.Rows.Add("Microsoft")
dt.Rows.Add("Linux")



Dim dict As New Dictionary(Of String, Integer)(dt.Rows.Count)

dt.Columns.Add(New DataColumn With {.ColumnName = "Col2", .DataType = GetType(Int32)})

For indx As Integer = 0 To dt.Rows.Count - 1
    Dim key As String = dt.Rows(indx)("Col1").ToString
    Dim value As Integer
    dict(key) = If(dict.TryGetValue(key, value), value + 1, value + 1)
    dt.Rows(indx)("Col2") = value + 1
Next

gives:

Super Stuff.....This is Exactly what is required...Thanks SPC and Every 1.

Thursday, July 21, 2016 3:29 PM

Super Stuff...Exactly the output required @ the Faster rate...Thank SPC and Every 1.