SQL Avg

The AVG() function is a SQL aggregate function that calculates the arithmetic mean (average) of a set of numeric values in a column. It’s one of the most commonly used functions in reporting and analytics — computing average price, average salary, average score, or average order value from rows in a table. Understanding exactly how AVG() treats NULL values, interacts with GROUP BY, and fits into the logical order of query execution is essential to getting correct results, not just syntactically valid ones.

Overview: How AVG Works

AVG() belongs to a family of SQL aggregate functions that also includes SUM(), COUNT(), MIN(), and MAX(). An aggregate function takes many input rows and collapses them into a single summary value (or one value per group, when combined with GROUP BY). Internally, the database engine evaluates the expression inside AVG() for every row in the working result set, keeps a running sum and a running count of the non-NULL values it has seen, and at the very end divides the sum by the count to produce the mean.

Two behaviors are critical to understand:

NULL values are excluded entirely — both from the sum and from the count used in the division. A row with a NULL in the averaged column is not treated as zero; it is simply skipped, as if it didn’t exist.
AVG() returns NULL, not zero or an error, when there are no non-NULL values to average — for example, averaging an empty result set, or a column where every value is NULL.

Without GROUP BY, AVG() produces exactly one row: the overall average across the whole (filtered) table. With GROUP BY, the engine partitions rows into buckets first, then computes a separate average for each bucket, producing one output row per group.

Syntax

The general form of AVG() is:

SELECT AVG(column_name) AS alias_name
FROM table_name
WHERE condition
GROUP BY grouping_column
HAVING AVG(column_name) > value
ORDER BY alias_name;

Part	Meaning
`AVG(column_name)`	Computes the mean of all non-NULL values of `column_name` in the current group (or the whole table if there’s no `GROUP BY`).
`AVG(DISTINCT column_name)`	Averages only the distinct values of the column — duplicate values are counted once.
`AS alias_name`	Gives the resulting column a readable name; without it many engines label the column something like `AVG(price)`.
`WHERE condition`	Filters individual rows before they are grouped or averaged. Cannot contain aggregate functions like `AVG()`.
`GROUP BY grouping_column`	Splits rows into buckets so `AVG()` is computed once per bucket instead of once overall.
`HAVING`	Filters groups after aggregation — this is where you can test the result of `AVG()`, unlike in `WHERE`.

Examples

Example 1: A simple overall average

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  price REAL
);

INSERT INTO products (product_id, product_name, category, price) VALUES
(1, 'Wireless Mouse', 'Electronics', 25.00),
(2, 'Keyboard', 'Electronics', 45.00),
(3, 'Monitor', 'Electronics', 150.00),
(4, 'Desk Lamp', 'Furniture', 20.00),
(5, 'Office Chair', 'Furniture', 120.00);

SELECT AVG(price) AS average_price
FROM products;

Result:

average_price
72.0

The engine sums every price (25 + 45 + 150 + 20 + 120 = 360) and divides by the number of rows (5), giving 72.0. No WHERE or GROUP BY is present, so there is exactly one result row covering the entire table.

Example 2: Average per group with GROUP BY

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category
ORDER BY category;

Result:

category	average_price
Electronics	73.33333333333333
Furniture	70.0

Here the three Electronics rows (25, 45, 150) average to 73.33…, and the two Furniture rows (20, 120) average to 70.0. GROUP BY category tells the engine to compute a separate sum and count for each distinct category value before dividing.

Example 3: Filtering groups with HAVING

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 70
ORDER BY average_price DESC;

Result:

category	average_price
Electronics	73.33333333333333

Both groups are computed first, but HAVING AVG(price) > 70 then discards any group whose average is not strictly greater than 70. Furniture’s average of exactly 70.0 fails that test, so only Electronics survives. This is the key reason HAVING exists: it lets you filter on an aggregate result, which WHERE cannot do.

Example 4: AVG ignores NULL values

CREATE TABLE feedback_scores (
  id INTEGER PRIMARY KEY,
  customer_name TEXT,
  score INTEGER
);

INSERT INTO feedback_scores (id, customer_name, score) VALUES
(1, 'Alice', 8),
(2, 'Bob', NULL),
(3, 'Carol', 6),
(4, 'Dave', 10);

SELECT AVG(score) AS avg_score, COUNT(*) AS total_rows, COUNT(score) AS scored_rows
FROM feedback_scores;

Result:

avg_score	total_rows	scored_rows
8.0	4	3

Bob’s score is NULL, so it is excluded from both the sum and the divisor: (8 + 6 + 10) / 3 = 8.0, not 24 / 4 = 6.0. Notice COUNT(*) counts all 4 rows including Bob’s, while COUNT(score) counts only the 3 rows with a non-NULL score — the same rule AVG() follows internally.

How It Works Step by Step (Under the Hood)

A query using AVG() is not executed in the order it’s written. SQL engines process a SELECT statement in this logical order:

FROM — identify the source table(s) and, if applicable, perform any joins.
WHERE — filter individual rows before any aggregation happens. Aggregate functions like AVG() cannot appear here, because no groups or sums exist yet at this stage.
GROUP BY — partition the remaining rows into buckets based on the grouping column(s). If there’s no GROUP BY, the entire result set is treated as one implicit group.
Aggregate functions evaluated — for each group, AVG() walks the rows in that group, accumulating a sum and a count of non-NULL values, then divides.
HAVING — filter out whole groups based on the aggregated values (this is the only place you can test AVG(...) > value).
SELECT — build the final output columns, including aliases like AS average_price.
ORDER BY — sort the final rows, which can reference the alias created in SELECT.
LIMIT — restrict the number of rows returned, if present.

This order explains why WHERE can’t use AVG() (aggregation hasn’t happened yet at that stage) but HAVING can (it runs after grouping and aggregation). It also explains why an alias defined in SELECT is usable in ORDER BY but not in WHERE — WHERE runs before SELECT even exists.

Common Mistakes

Mistake 1: Using AVG() inside WHERE

SELECT *
FROM employees
WHERE AVG(salary) > 50000;

This fails because WHERE filters individual rows before any aggregation occurs — there is no aggregate value yet for AVG() to compare. SQLite raises an error such as misuse of aggregate function AVG(). To filter on an average, either aggregate first and filter with HAVING, or compute the average in a subquery:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Mistake 2: Assuming AVG(DISTINCT column) behaves like AVG(column)

CREATE TABLE order_amounts (
  id INTEGER PRIMARY KEY,
  amount REAL
);

INSERT INTO order_amounts (id, amount) VALUES
(1, 50.00),
(2, 50.00),
(3, 75.00),
(4, 100.00);

SELECT AVG(amount) AS avg_all, AVG(DISTINCT amount) AS avg_distinct
FROM order_amounts;

Result:

avg_all	avg_distinct
68.75	75.0

AVG(amount) averages all four rows (50, 50, 75, 100) for 68.75. AVG(DISTINCT amount) first removes the duplicate 50, leaving only (50, 75, 100), which averages to 75.0. Using DISTINCT when you actually wanted every row counted — or forgetting it when duplicates should only count once — silently changes your result without throwing any error, which makes it a dangerous mistake to overlook.

Mistake 3: Treating a long decimal as a display bug

As seen in Example 2, AVG(price) for Electronics returned 73.33333333333333. This isn’t a bug — floating-point division simply doesn’t terminate cleanly. Always round explicitly for display rather than truncating the value in application code:

SELECT category, ROUND(AVG(price), 2) AS avg_price_rounded
FROM products
GROUP BY category
ORDER BY category;

Result:

category	avg_price_rounded
Electronics	73.33
Furniture	70.0

Best Practices

Always be intentional about NULLs — if you need a true zero-inclusive average, use SUM(COALESCE(column, 0)) / COUNT(*) instead of AVG(column), since AVG() silently skips NULLs.
Use HAVING, never WHERE, to filter on the result of AVG() or any other aggregate function.
Wrap AVG() in ROUND() when displaying results to users, since raw floating-point division often produces long decimals.
Give the aggregated column a clear alias with AS — reports and downstream code should never depend on an engine-generated name like AVG(price).
Remember that an AVG() over zero rows (or an all-NULL column) returns NULL, not 0 — check for this in application logic before dividing or displaying the value.
When you need both a total count and an average, pair AVG() with COUNT() in the same query so you can see how many rows actually contributed to the average.

Practice Exercises

Exercise 1: Using a table employees(id, name, department, salary, hire_date), write a query that returns the average salary for each department, sorted from highest average to lowest.
Exercise 2: Using the same employees table, write a query that returns only the departments whose average salary exceeds 60000. (Hint: you will need both GROUP BY and HAVING.)
Exercise 3: Using the orders(order_id, customer_id, order_date, amount) table, write a query that returns the overall average order amount rounded to 2 decimal places, aliased as avg_order_amount.

Summary

AVG() computes the arithmetic mean of a numeric column across a set of rows.
NULL values are excluded from both the sum and the count used by AVG() — they are not treated as zero.
Without GROUP BY, AVG() returns one overall value; with GROUP BY, it returns one average per group.
Use HAVING, not WHERE, to filter based on an aggregate like AVG(), because WHERE runs before aggregation in the logical query order.
AVG(DISTINCT column) averages only unique values, which can produce a very different result from AVG(column).
AVG() over zero non-NULL values returns NULL, not 0.

SQL Avg

Overview: How AVG Works

Syntax

Examples

Example 1: A simple overall average

Example 2: Average per group with GROUP BY

Example 3: Filtering groups with HAVING

Example 4: AVG ignores NULL values

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Using AVG() inside WHERE

Mistake 2: Assuming AVG(DISTINCT column) behaves like AVG(column)

Mistake 3: Treating a long decimal as a display bug

Best Practices

Practice Exercises

Summary

Table of Contents