How can I calculate an average while ignoring missing values?

Use AVG(rating) grouped by product, and pair it with COUNT(rating) so the result shows how many actual ratings contributed:

SELECT
  product,
  AVG(rating) AS average_rating,
  COUNT(rating) AS ratings_used
FROM reviews
GROUP BY product;

SQLite’s AVG(rating) averages the non-NULL ratings in each group; it does not include NULL as a zero. COUNT(rating) counts those same non-NULL values, whereas COUNT(*) counts every row, including rows whose rating is NULL. [S1]

For example, suppose the table contains these illustrative rows:

product  rating
A        4
A        NULL
A        2
B        NULL

The result is:

product  average_rating  ratings_used
A        3.0             2
B        NULL            0

The average for A is based on 4 and 2. B has no available rating, so SQLite returns NULL for its average. [S1] The count makes that case visible instead of leaving a reader to mistake it for an average of zero.

Because COUNT(*) answers a different question: how many rows are in the group. It counts a row even when its rating is NULL. COUNT(rating) counts only rows where rating is non-NULL, so it matches the values used by AVG(rating). [S1]

Using the example above, product A has three rows but only two ratings. Product B has one row but no ratings. Thus, pairing AVG(rating) with COUNT(rating) reports the average and its available sample size; pairing it with COUNT(*) reports the average and the number of records instead.

If you need both figures, you can include both counts and label them clearly:

SELECT
  product,
  AVG(rating) AS average_rating,
  COUNT(rating) AS ratings_used,
  COUNT(*) AS rows_in_group
FROM reviews
GROUP BY product;

For this small example, A would show 2 ratings used and 3 rows in the group; B would show 0 ratings used and 1 row in the group.

SQLite returns NULL from AVG when a group has no non-NULL inputs. [S1] A displayed zero could be read as an actual average rating of zero, even though no ratings were available. Keeping the NULL and showing ratings_used = 0 preserves that distinction.

If a report requires a numeric value in that situation, make clear that zero is a display convention, not an observed average. Keep the count beside it so readers can see whether any ratings contributed. If consumers might confuse the two, leave the average as NULL.

Confirm that the column you average contains numeric ratings or NULLs. SQLite also treats nonnumeric text or BLOB values as 0 in AVG, so unexpected values can affect the result rather than being ignored as missing. [S1] The query assumes that NULL is how your data represents a missing rating; it does not determine whether your data uses another convention, such as a text label.

Then check that the grouping column is the one you want to report by, and keep COUNT(rating) in the output so readers can see the number of available ratings. For the example query, that count is the number of non-NULL ratings in each product group, not the total number of review rows.

Reusable pattern:

SELECT
  group_column,
  AVG(numeric_value_column) AS average_value,
  COUNT(numeric_value_column) AS values_used
FROM table_name
GROUP BY group_column;

Replace the example identifiers with your actual table and column names. If you need to report total records too, add COUNT(*) AS rows_in_group and label it separately.