The result of all our aggregations was a single value. This single value can also be used as a scalar to perform operations on more advanced queries. These scalars are used in the form of subqueries.

A subquery is a query within a query, in simple terms. The result of one query is used to obtain the result of another query.

For example, the result obtained from the averaging query can be used to selectively print only those temperatures that were above average:

    SELECT * FROM temperature
    WHERE value > (SELECT avg(value) FROM temperature);

In the preceding query, (SELECT avg(value) FROM temperature) is executed first, and the value obtained from it (since it is one-dimensional) is used to specify the filter for the higher-level query. This query will give us the following result:

In addition to filtering, we can also use subqueries to modify the displayed results of a normal query. To illustrate this, consider the following query, which gives us the deviation of each temperature from the average, in addition to the information we already receive:

    SELECT value, 
    (SELECT avg(value) FROM temperature) - value,
    createdAt
    FROM temperature;

In the second line, we direct the query to give us an additional column, which is the difference in the average (whose value is evaluated from the subquery) and the value of the temperature in each row. We then get the resulting table:

The power of SQLite seems more apparent now since we just displayed information that wasn't even present in our table.