Lesson 9	SQL `AVG` function
Objective	Understand the use of the `AVG` function in SQL

SQL `AVG` Function

The SQL AVG function calculates an arithmetic mean for a numeric expression across a set of rows. You will use AVG constantly in reporting and analysis to answer questions such as: “What is the average sale amount?”, “What is the average latency?”, or “Which products are above the average?”

Syntax

AVG(expr)

expr can be a column, literal, or numeric expression (for example, price * 2). Most SQL platforms ignore NULL values for AVG, so only non-NULL values contribute to the mean.

Basic example

The simplest pattern is to select a single average for a filtered set of rows.

SELECT AVG(MyNumber) AS avg_number
FROM MyTable;

If you also select non-aggregated columns, you usually need GROUP BY.

Average per group

Use GROUP BY when you want one average per category (department, product, region, etc.).

SELECT DepartmentId,
       AVG(Salary) AS avg_salary
FROM Employees
GROUP BY DepartmentId
ORDER BY avg_salary DESC;

This returns one row per department, with an average computed from that department’s rows.

How `AVG` treats `NULL`

A frequent source of confusion is NULL. In most SQL engines, AVG ignores NULL values. That means the denominator is the count of non-NULL values, not the total row count.

-- Average of non-NULL scores only
SELECT AVG(Score) AS avg_score
FROM ExamResults;

-- If you intentionally want NULL treated as 0, make that explicit
SELECT AVG(COALESCE(Score, 0)) AS avg_score_including_null_as_zero
FROM ExamResults;

Also note: if your filter produces no rows (or all rows have NULL in expr), the result is typically NULL (not 0).

Controlling precision (avoiding “integer-style” averages)

When the input expression is an integer type, some platforms may produce an average that looks truncated or rounded. The safest, cross-platform habit is to cast the input to a decimal type when you care about fractional precision.

SELECT AVG(CAST(qty AS decimal(18,2))) AS avg_qty
FROM OrderLines;

You can then format the output using ROUND if desired.

SELECT ROUND(AVG(CAST(qty AS decimal(18,2))), 2) AS avg_qty_2dp
FROM OrderLines;

`AVG` with subqueries (“above average”)

A classic use of AVG is selecting rows that exceed the overall average.

SELECT title_id, sales
FROM titles
WHERE sales > (SELECT AVG(sales) FROM titles)
ORDER BY sales DESC;

Worked example set (what each query demonstrates)

The following query set demonstrates common AVG patterns: (1) averaging an expression, (2) what happens when a filtered set contains no rows, and (3) above-average filtering.

-- 1) Average of a derived numeric expression
SELECT AVG(price * 2) AS avg_price_doubled
FROM titles;

-- 2) Average and total on a filtered set (may return NULLs if no matching rows exist)
SELECT AVG(sales) AS avg_sales,
       SUM(sales) AS total_sales
FROM titles
WHERE type = 'business';

-- 3) List rows above the overall average
SELECT title_id, sales
FROM titles
WHERE sales > (SELECT AVG(sales) FROM titles)
ORDER BY sales DESC;

If there are no business rows in titles, then both AVG(sales) and SUM(sales) evaluate to NULL in many SQL implementations because there are no rows to aggregate.

Summary

AVG(expr) returns an arithmetic mean over the selected rows.
AVG typically ignores NULL values in expr.
Use GROUP BY to compute one average per category.
Cast to a decimal type when you require predictable fractional precision.
Use subqueries to filter values “above average.”

SQL AVG Function