Monthly Archives: April 2009

Group By and Aggregates in .NET DataTable

Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:

Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable


        Dim dv As New DataView(i_dSourceTable)

        'getting distinct values for group column
        Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn}) 

        'adding column for the row count
        dtGroup.Columns.Add("Count", GetType(Integer)) 

        'looping thru distinct values for the group, counting
        For Each dr As DataRow In dtGroup.Rows
            dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
        Next        

        'returning grouped/counted result
        Return dtGroup
End Function

The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s “ToTable” method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s “Compute” method – Count in this case, but it can easily be replaced with other aggregates.

TSQL IsNumeric function returns false positives

IsNumeric(data)  is a widely used function in SQL Server programming.  If passed in data is a number – it returns 1, otherwise 0. Supposedly. There’re several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign “$”, minus sign “-“, space, tab. If you try to call Cast or Convert function on the data after that to convert data to number – they will throw an error

An excellent article at SQL Hacks suggest adding “e0” to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.

Which works great until actual number in scientific format is passed.  If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding “e0” as article suggest IsNumeric(1.2345e5e0) returns 0.

To work around this limitation I simple test for an “e” within passed data. If it exist, I am calling IsNumeric as usual, if it does not – I am adding “e0” to the tested data:

CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */

RETURNS int
AS
BEGIN
       DECLARE @iResult int

      — if there is ‘E’ in the value – do a standard IsNumeric test
     IF CHARINDEX(‘E’, UPPER(@i_sValueToTest))<> 0
            SET @iResult = IsNumeric(@i_sValueToTest)
      — Otherwise do a modified IsNumeric test by adding ‘E0’ at the end
    ELSE
          SET @iResult = IsNumeric(@i_sValueToTest + ‘E0’)

        RETURN @iResult
END