User Defined Aggregates

You can use sqlite_create_aggregate to register a mIRC alias as a SQL aggregate. Please look at the reference to see an explanation of the parameters.
User defined aggregates are very similiar to regular functions, so only the differences will be explained here, see User Defined Functions for more information. The key difference is that you need to actually register two mIRC aliases to create an aggregate. SQL aggregates are used to, as the name implies, aggregate data from rows, thus the need of two different aliases: One that keeps track of the aggregating, called the step function, and one that returns the final result of aggregation at the end, called finalizer function.

mSQLite passes an aggregation context to the step alias. The step alias can use this context to accumulate the result, and at the end of the alias it should return the new aggregation context.
Here's an example step alias for keeping track of max length of a string:

Code alias max_len_step {   var %context = $1, %len = $len($2)   if (%context !isnum || %len > %context) {     %context = %len   }   return %context }

The %context !isnum check is important. The reason for doing this is because the first time the step alias is called, %context is set to $null. %len > %context would always fail if we didn't do the check.

After the aggregating is done, eg. when all rows in a group are processed, mSQLite will call the finalizer alias. The finalizer will receive the aggregation context that the step alias used to aggregate data as an argument. The finalizer should then return whatever it wants to return as a final value from the aggregate function. In our example we simply return the max length:

Code alias max_len_finalize {   var %context = $1   if (%context !isnum) {     %context = 0   }   return %context }

Once again we check if the aggregation context is number or not. We do this because if there are no rows selected in the query, the step alias is never called, thus %context can be $null when finalizer alias is called.

NOTE. As of version 1.0.14 you can specify distinct properties for step and finalize aliases, whereas before you could only create an aggregate with same property for both aliases. This means that you can now write custom aggregates by writing only one alias in mIRC by checking the $prop identifier's value:

Code ; An example alias that is used as both step and finalize alias ; You can use this to register the aggregate correspondingly: ; /sqlite_create_aggregate %db max_len max_len max_len 1 step finalize alias max_len {   ; Both step and finalize alias needs to check if context isn't set yet   var %context = $1   if (%context !isnum) {     %context = 0   }   ; If $prop is step we're in step alias, otherwise in finalize alias   if ($prop == step) {     var %len = $2     if (%len > %context) {       %context = %len     }   }   ; Again, both step and finalize alias should return the context at the end   return %context }

Here's a complete example of implementing the aggregate and example of using it:

Example /* ** mIRC SQLite Demonstration Script **              MAX_LEN Aggregate ** -------------------------------- ** Save as sqlite_aggregate.mrc and do ** /load -rs sqlite_aggregate.mrc ** ** This script demonstrates the user ** defined aggregates of the SQLite. ** ** Usage: **  /find_max_len word1 word2 word3 ... wordN ** ** The /find_max_len will find the maximum ** length of a word from the list of words ** passed to it. */ ; Our test alias alias find_max_len {   var %db = $sqlite_open(words.db)   if (!%db) {     echo 4 -a Error: %sqlite_errstr     return   }   ; Do initialize stuff   sqlite_create_aggregate %db max_len max_len_step max_len_finalize 1   sqlite_exec %db DROP TABLE IF EXISTS words   sqlite_exec %db CREATE TABLE words (word)   var %i = 1   while (%i <= $0) {     var %word = $sqlite_escape_string($gettok($1-, %i, 32))     sqlite_exec %db INSERT INTO words (word) VALUES (' $+ %word $+ ')     inc %i   }   ; Find the longest length   var %sql = SELECT max_len(word) FROM words   var %request = $sqlite_query(%db, %sql)   ; Aggregate functions always returns something,   ; so we don't need to check if there are rows   var %len = $sqlite_fetch_single(%request)   echo -a The maximum lenght of a word is %len characters.   sqlite_free %request   sqlite_close %db } ; Step alias alias max_len_step {   var %context = $1, %len = $len($2)   if (%context !isnum || %len > %context) {     %context = %len   }   return %context } ; Finalizer alias alias max_len_finalize {   var %context = $1   if (%context !isnum) {     %context = 0   }   return %context }