locked
datatable.compute(count("col1"), "col1 Is Null") return 0 RRS feed

  • Question

  • I want to get the number of rows where col1 is Null. datatable.compute(count("col1"), "col1 Is Null") always returns 0, and I am sure there are more than 1 rows with col1 is null. What is my problem?

    Thanks,
    Friday, May 15, 2009 9:12 PM

Answers

  • I assume you meant "count(col1)" rather than count("col1"). But even that returns 0 in combination with the "col1 is Null" expression.

    I haven't found DataColumn.Expression to be highly intuitive. It's not quite like SQL. It does support the ISNULL function, but I couldn't get that to work right either.

    As an alternative, you could use DataTable.Select to get an array of rows that match an expression. Then you could use the .Count property of that array to get the number of matches. For example:

            Dim table As DataTable = New DataTable
            table.Columns.Add(New DataColumn("col1", GetType(String)))
    
            For i As Integer = 1 To 5
                table.Rows.Add()
            Next i
    
            Console.WriteLine("Found {0} rows where column1 is null", _
                table.Select("col1 is null").Count)
    
            Console.ReadKey()
    • Marked as answer by topcan5 Saturday, May 16, 2009 9:34 PM
    Saturday, May 16, 2009 4:26 AM
  • > I want to get the number of rows where col1 is Null. datatable.compute(count("col1"), "col1 Is Null") always returns 0

    That is because the count() aggregate does not count null values.

    Pass a column that does not contain nulls into the count() aggregate.  The primary key should work.

    datatable.compute("count(yourPrimaryKey)", "col1 Is Null")
    • Proposed as answer by Matt Fisher Saturday, May 16, 2009 5:13 AM
    • Marked as answer by topcan5 Saturday, May 16, 2009 9:34 PM
    Saturday, May 16, 2009 4:29 AM

All replies

  • I assume you meant "count(col1)" rather than count("col1"). But even that returns 0 in combination with the "col1 is Null" expression.

    I haven't found DataColumn.Expression to be highly intuitive. It's not quite like SQL. It does support the ISNULL function, but I couldn't get that to work right either.

    As an alternative, you could use DataTable.Select to get an array of rows that match an expression. Then you could use the .Count property of that array to get the number of matches. For example:

            Dim table As DataTable = New DataTable
            table.Columns.Add(New DataColumn("col1", GetType(String)))
    
            For i As Integer = 1 To 5
                table.Rows.Add()
            Next i
    
            Console.WriteLine("Found {0} rows where column1 is null", _
                table.Select("col1 is null").Count)
    
            Console.ReadKey()
    • Marked as answer by topcan5 Saturday, May 16, 2009 9:34 PM
    Saturday, May 16, 2009 4:26 AM
  • > I want to get the number of rows where col1 is Null. datatable.compute(count("col1"), "col1 Is Null") always returns 0

    That is because the count() aggregate does not count null values.

    Pass a column that does not contain nulls into the count() aggregate.  The primary key should work.

    datatable.compute("count(yourPrimaryKey)", "col1 Is Null")
    • Proposed as answer by Matt Fisher Saturday, May 16, 2009 5:13 AM
    • Marked as answer by topcan5 Saturday, May 16, 2009 9:34 PM
    Saturday, May 16, 2009 4:29 AM