Common table expression functionality in SQLite

Question

I need to apply two successive aggregate functions to a dataset (the sum of a series of averages), something that is easily and routinely done with common table expressions in SQL Server or another DBMS that supports CTEs. Unfortunately, I am currently stuck with SQLite which does not support CTEs. Is there an alternative or workaround for achieving the same result in SQLite without performing two queries and rolling up the results in code?

To add a few more details, I don't think it could be easily done with views because the first set of aggregate values need to be retrieved based on a WHERE clause with several parameters. E.g.,

SELECT avg(elapsedTime)
FROM statisticsTable
WHERE connectionId in ([lots of values]) AND 
      updateTime > [startTime] AND
      updateTime < [endTime]
GROUP BY connectionId

And then I need the sum of those averages.

Answer 1

As you've mentioned, SQLite doesn't support CTEs, window functions, or any of the like.

You can, however, write your own user functions that you can call inside SQLite by registering them to the database with the SQLite API using sqlite_create_function(). You register them with the database, and then you can use them in your own application code. You can make an aggregate function that would perform the sum of a series of averages based on the individual column values. For each value, a step-type callback function is called that allows you to perform some calculation on the data, and a pointer for holding state data is also available.

In your SQL, then, you could register a custom function called sum_of_series_of_averages and have:

SELECT sum_of_series_of_averages(columnA,columnB)
FROM table
WHERE ...

For some good examples on how those work, you should check out the SQLite source code, and also check out this tutorial (search for Defining SQLite User Functions).

Answer 2

Would this work?

SELECT SUM(t.time) as sum_of_series_of_averages
FROM
(
SELECT avg(elapsedTime) as time
FROM statisticsTable
WHERE connectionId in ([lots of values]) AND 
      updateTime > [startTime] AND
      updateTime < [endTime]
GROUP BY connectionId
) as t

By converting your averages into an inline view, you can SUM() the averages.

Is this what you are looking for?