How do I add a custom aggregate function using TFDSQLiteFunction?

I want to create a custom function to use in this way:

FDQuery1->SQL->Text = "SELECT SQRT_SUM(FIELD_1) FROM TABLE_1 WHERE ID = 1";
FDQuery1->Open();

ShowMessage(Form1->FDQuery1->Fields->Fields[0]->AsFloat);

FDQuery1->Close();

I need standard deviation and other statistical functions, but for simplicity's sake let's go with "square root of the sum". Based on "Using SQLite with FireDAC" tutorial I added:

void __fastcall TForm1::FDSQLiteFunction1Calculate(TSQLiteFunctionInstance *AFunc,
          TSQLiteInputs *AInputs, TSQLiteOutput *AOutput, TObject *&AUserData)

{
    double sum = 0;
    for (long i = 0; i < AInputs->Count; i++) sum += AInputs->Inputs[i]->AsFloat;
    AOutput->AsFloat = sqrt(sum);
}

FDSQLiteFunction1->DriverLink = FDPhysSQLiteDriverLink1;
FDSQLiteFunction1->FunctionName = 'SQRT_SUM';
FDSQLiteFunction1->Aggregated = true;
FDSQLiteFunction1->ArgumentsCount = 1;
FDSQLiteFunction1->OnCalculate = FDSQLiteFunction1Calculate;
FDSQLiteFunction1->Active = true;

But the function is called for each row, not once, and I can't get the value I want unless I use global variables. How do I do a for loop to sum all values?

Solution

How do I do a for loop to sum up all the values?

You don't. You have misunderstood how the OnCalculate event works in your situation.

For a non-aggregate function, the OnCalculate event indeed works as you are expecting. It is called only 1 time, with AInputs containing all of the input values. The event must output the final calculated value.

However, for an aggregate function, the OnCalculate event gets called for each step (ie record) while SQLite is iterating through the dataset that is being aggregated, and thus AInputs will contain only the input values of the record in the current step. The OnCalculate event cannot return a final calculated value yet, so you must preserve the current calculation through each step and then use the OnFinalize event to calculate the final value once the iteration has finished.

Refer to SQLite's documentation - Application-Defined SQL Functions , and in particular the section on Callbacks.

Try something more like this:

void __fastcall TForm1::FDSQLiteFunction1Calculate(TSQLiteFunctionInstance *AFunc,
    TSQLiteInputs *AInputs, TSQLiteOutput *AOutput, TObject *&AUserData)
{
    // I'm not sure if FireDAC provides a nicer wrapper for this call!
    double* sum = static_cast<double*>(sqlite3_aggregate_context(AOutput->Handle, sizeof(double)));
    if (!sum)
    {
        AOutput->ErrorCode = ...;
        AOutput->ErrorText = ...;
        return;
    }
    //

    *sum += AInputs->Inputs[0]->AsFloat;
}

void __fastcall TForm1::FDSQLiteFunction1Finalize(TSQLiteFunctionInstance *AFunc,
    TObject *&AUserData)
{
    // I'm not sure if FireDAC provides a nicer wrapper for this call!
    double* sum = static_cast<double*>(sqlite3_aggregate_context(AOutput->Handle, 0));
    //

    AFunc->Output->AsFloat = sum ? sqrt(*sum) : 0.0;
}

FDSQLiteFunction1->DriverLink = FDPhysSQLiteDriverLink1;
FDSQLiteFunction1->FunctionName = 'SQRT_SUM';
FDSQLiteFunction1->Aggregated = true;
FDSQLiteFunction1->ArgumentsCount = 1;
FDSQLiteFunction1->OnCalculate = FDSQLiteFunction1Calculate;
FDSQLiteFunction1->OnFinalize = FDSQLiteFunction1Finalize;
FDSQLiteFunction1->Active = true;