title: Common table expression functionality in SQLite
body: <p>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?</p>
  
  <p>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., </p>
  
  <pre><code>SELECT avg(elapsedTime)
  FROM statisticsTable
  WHERE connectionId in ([lots of values]) AND 
        updateTime &gt; [startTime] AND
        updateTime &lt; [endTime]
  GROUP BY connectionId
  </code></pre>
  
  <p>And then I need the sum of those averages.</p>