AVG

AVG returns the arithmetic mean of non-null numeric values.

It helps summarize central tendency for metrics like order value or score.

Core Concepts

flowchart TD
  A[Numeric values] --> B[Ignore NULL values]
  B --> C[Sum values]
  C --> D[Divide by non-NULL count]
  D --> E[Average result]

Expression	Meaning
AVG(score)	Mean of non-null score values
AVG(DISTINCT score)	Mean of unique non-null values

Code Examples

-- Setup score data with one NULL.
CREATE TABLE scores_avg (
  student TEXT NOT NULL,
  score   REAL
);

INSERT INTO scores_avg (student, score)
VALUES
  ('Ava', 90),
  ('Ben', 70),
  ('Chen', NULL),
  ('Dina', 80);

Expected output
Table created and four rows inserted (one NULL score).

-- Average excludes NULL score.
SELECT AVG(score) AS avg_score
FROM scores_avg;

avg_score
80.0

-- Compare with explicit count of non-null values.
SELECT
  COUNT(score) AS counted_scores,
  AVG(score)   AS avg_score
FROM scores_avg;

counted_scores	avg_score
3	80.0

SQLite-Specific Nuances

SQLite Nuance

AVG() ignores NULL values in SQLite, which can make averages look higher/lower than expected if many rows are missing data.

Common Pitfalls / Best Practices

Pitfall

Treating missing values as zeros without deciding whether that is logically correct.

Best Practice

Always pair AVG() with a count check (COUNT(column)) so readers know how many values were averaged.

Quick Challenge

Compute average score for rows where score is at least 80.

View Solution

SELECT AVG(score) AS avg_high_score
FROM scores_avg
WHERE score >= 80;

avg_high_score
85.0