How can I use a scalar subquery to show a related summary beside each row?

Yes. In SQLite, place a scalar subquery in the SELECT list. The outer query still returns product rows, while the subquery calculates a value to display alongside each one:

SELECT
  p.name,
  p.category,
  p.price,
  (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category = p.category
  ) AS category_avg
FROM products AS p;

The key comparison is p2.category = p.category. p2 names rows being examined inside the subquery; p names the current row from the outer query. SQLite permits a scalar subquery to refer to outer-query columns. Since the subquery returns one column, its result can be used as a value in the SELECT list. [S1]

For an illustrative dataset, assume the products are “Mug” at 10 and “Bottle” at 14 in “Drinkware,” and “Notebook” at 6 in “Paper.” The result would pair Mug with 12, Bottle with 12, and Notebook with 6 as their category averages. These names and prices are made-up examples, not data from a real store.

The outer row supplies the changing value. When the outer row is Mug, p.category is “Drinkware,” so the subquery’s filter compares p2.category with “Drinkware.” For Notebook, p.category is “Paper,” so the filter instead selects rows in “Paper.” That reference from inside the subquery to p.category outside it is the correlation.

You can read the names as separate roles: p is the product currently being returned; p2 is a candidate product considered for the summary. The AVG is calculated over the prices that pass the category match. SQLite describes correlated subqueries as being reevaluated whenever their result is required. That describes SQL semantics; it does not establish how fast the query will run or how another database handles it. [S1]

A scalar subquery is used as a single value in an expression. SQLite’s rule is that a one-column subquery supplies the first row’s value, or NULL if it returns no rows. [S1] In this example, AVG provides a summary of the prices that match the category. For a non-aggregate subquery that can return multiple rows, the SQLite rule about taking the first row matters, so make sure the result you intend is genuinely one value rather than relying on a particular match.

In the shown query, a product normally matches its own category because its row is in products. If your real query uses a different table or additional filters, it may return no rows. In that case, SQLite’s scalar-subquery rule gives a NULL result. Decide whether that is appropriate for your report rather than treating it as zero.

Replace products, name, category, and price with your actual table and column names, keeping the two aliases distinct. Then check the intended comparison group: if the summary should be by category, compare categories; if it should be by another grouping, correlate on that field instead. For the made-up example, the hand-calculated check is that both Drinkware rows show 12, while the Paper row shows 6.

Also inspect which rows contribute to the average. A filter added inside the subquery changes which prices contribute; a filter added only to the outer query changes which product rows are displayed. The SQLite description establishes the subquery behavior above, but does not establish performance, indexing needs, or whether other SQL engines use identical semantics. Check the query with a small set of rows whose expected result you can calculate by hand.