Using eXtremeSQL Aggregate functions in C++

Aggregate functions are often used to perform operations on a column of a result set. The standard aggregate functions supplied by eXtremeSQL are listed in the following table. Note that all aggregate functions return the type of the value stored in that table column.

Aggregate Function	Description
avg(column_name)	Returns the average of all values in the specified column.
count(*)	Returns the count of rows in the result set.
max(column_name)	max(column_name) Returns the maximum value in the specified column.
min(column_name)	Returns the minimum value in the specified column.
sum(column_name)	Returns the sum of all values in the specified column.

The following code snippet demonstrates the use of built-in aggregate functions:

     
    void queryContributions()
    {
        // Execute query calculating average age of all persons
        QueryResult result(engine.executeQuery("select avg(amount), min(amount),
        max(amount), sum(amount) from Contributions"));
     
        // Get cursor
        Cursor* cursor = result->records();
         
        // Result data source consists of one record with single column.
        // Indices of columns starts from 0
        Record* rec = cursor->next();
        Value* avg = rec->get(0);
        Value* min = rec->get(1);
        Value* max = rec->get(2);
        Value* sum = rec->get(3);
         
        printf("\n\tAverage Contribution: %f\n", (float)avg->realValue());
        printf("\n\tMinimum Contribution: %f\n", (float)min->realValue());
        printf("\n\tMaximum Contribution: %f\n", (float)max->realValue());
        printf("\n\tTotal of Contributions: %f\n", (float)sum->realValue());
    }
     

 

User-defined aggregate functions

eXtremeSQL also allows developers to create their own user-defined aggregate functions. The user-defined function (UDF) must be defined as returning type static Value* and can accept arguments of type Value*. Then it must be “registered” with the eXtremeSQL runtime by declaring a function of type static SqlFunctionDeclaration udf () with arguments specifying the type returned, the name used to call it, a pointer to the function and the number of arguments required.

The following code snippet defines then registers a UDF:

     
    // here is the implementation of the UDF:
    static Value* mod(Value* a, Value* b) 
    {
        if (a->isNull() || b->isNull()) 
        {
            return NULL;
        }
        return new IntValue(a->intValue() % b->intValue());
    }
     
    // below, f1 is an instance of the SqlFunctionDeclaration class.  The
    // constructor links this declaration of a UDF named “mod” to the list
    // of all UDFs maintained internally by eXtremeSQL:
     
    static SqlFunctionDeclaration f1
    (
        tpInt,      // tpInt is the return value of the UDF
        "mod",      // the name of the function as we’ll use it in a query
        (void*)mod, // the function pointer
        2          // the number of arguments to the UDF
    );
     

The UDF can then be called in a normal SQL select statement. For example, the following statement selects records where the value of the column named code is divisible by 3.

     
    select * from T where mod(code,3) = 0;