SQLite aggregate function with complex state example

aggavg.c

// gcc -shared aggavg.c -o aggavg.dll -s -static
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

typedef struct agg_state {
	int cnt;
	double sum;
} agg_state;

static void avg_step (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
	agg_state* state = (agg_state*)sqlite3_aggregate_context(ctx, sizeof(agg_state));
	if (state == NULL) 
		return sqlite3_result_error_nomem(ctx);

	state->cnt++;
	state->sum += sqlite3_value_double(argv[0]);	
}

static void avg_final (sqlite3_context *ctx) {
	agg_state* state = (agg_state*)sqlite3_aggregate_context(ctx, sizeof(agg_state));
	if ((double)state->cnt > 0)
		sqlite3_result_double(ctx, state->sum / (double)state->cnt);
	else
		sqlite3_result_null(ctx);
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_aggavg_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
	SQLITE_EXTENSION_INIT2(pApi);
	return sqlite3_create_function (db, "aggavg", 1, SQLITE_UTF8, NULL, NULL, avg_step, avg_final);
}