Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from Using SQLite

Aggregate Functions

Aggregate functions are used to collapse values from a grouping of rows into a single result value. This can be done with a whole table, as is common with the aggregate function count(*), or it can be done with groupings of rows from a GROUP BY clause, as is common with something like avg() or sum(). Aggregate functions are used to summarize, or aggregate, all of the individual row values into some single representative value.

Defining Aggregates

SQL aggregate functions are created using the same sqlite3_create_function_xxx() function that is used to create scalar functions (See Scalar Functions). When defining a scalar function, you pass in a C function pointer in the sixth parameter and set the seventh and eighth parameter to NULL. When defining an aggregate function, the sixth parameter is set to NULL (the scalar function pointer) and the seventh and eighth parameters are used to pass in two C function pointers.

The first C function is a “step” function. It is called once for each row in an aggregate group. It acts similarly to an scalar function, except that it does not return a result (it may return an error, however).

The second C function is a “finalize” function. Once all the SQL rows have been stepped over, the finalize function is called to compute and set the final result. The finalize function doesn’t take any SQL parameters, but it is responsible for setting the result value.

The two C functions work together to implement the SQL aggregate function. Consider ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content