UDF in C# that updates a table (dividing all data with result of query)

Question

Is there a way to divide each data of a table (all columns and rows) by

select count(*) from table;

so for example

table

     a1  a2  a3   a4
   -------------------
    438 498 3625 3645
    500 291 5000 2351
    233 263 1298 1687
    198 117 1744 5932
    438 498 3625 3648
    500 291 5000 2637

and then divide each of them by 6 which is the number of rows

     a1    a2   a3      a4
   ------------------------
   73.0  83.0 604.16  607.5
   83.33 48.5 833.33  391.83
   ...
   ...
   ...
   ...

One problem is that my data are INT, but after transform they would be double...

In c# I was doing:

int N = 0;
string sql = @"select count(*) from 'ExampleTable'";
N  = (int)cmd.ExecuteScalar();

int Columns = 0;
sql = @"select count(*) from information_schema.COLUMNS WHERE TABLE_NAME = 'ExampleTable'";

Columns  = (int)cmd.ExecuteScalar();


cmdLoad.CommandText = "SELECT * FROM [ExampleTable]";
int i,j;
double[,] newTable  = new double[N, Columns];
using (SqlDataReader reader = cmdLoad.ExecuteReader())
{
   while (reader.Read())
    {
        for ( i = 1; i <= N; j++)
        {
           for ( j = 1; j <= Columns; j++)
          {
              newTable[i,j] = reader.GetDouble((i*j) - 1) / N ;
          } //j  
        } //i
     }//reader
}

but I am not sure...

score 1 · Accepted Answer · answered Oct 24 '11 at 02:29

If you know the columns in advance, it's pretty easy:

SELECT 
    a1 / Rows As a1, 
    a2 / Rows As a2, 
    a3 / Rows As a3, 
    a4 / Rows As a4
FROM 
(
    SELECT *, CAST(COUNT(*) OVER (PARTITION BY NULL) as float) As Rows
    FROM ExampleTable
) Data

If not, you'll need to do effectively what you're doing in C# - loop through the columns and build a dynamic query

DECLARE @TableName varchar(100)
DECLARE @Query varchar(max)
SELECT @TableName = 'ExampleTable'
SELECT @Query = 'SELECT '
SELECT @Query = @Query + '[' + COLUMN_NAME + '] / Rows as [' + COLUMN_NAME + '], '
FROM information_schema.COLUMNS col WHERE TABLE_NAME = @TableName

SELECT @Query = @Query + 'Rows FROM ( SELECT *, CAST(COUNT(*) OVER (PARTITION BY NULL) as float) As Rows FROM [' + @TableName + ']) Data'
EXECUTE(@Query)

UDF in C# that updates a table (dividing all data with result of query)

1 Answers1