Introduction
An average (or arithmetic mean) is one of the most widely used statistical measures in business, finance, and analytics. It tells you the typical central value of a numeric dataset:
- Average employee salary: Understanding payroll benchmarks.
- Average product price: Pricing competitiveness and catalog positioning.
- Average customer order value: Measuring customer spending health.
- Average student test marks: Evaluating cohort performance.
In plain language:
What Is AVG?
AVG() is a standard SQL aggregate function that computes the arithmetic mean across all numeric inputs in a column or expression:
FROM employees;
Basic AVG Syntax
The expression is evaluated for each row, and all non-NULL results are summed and divided by the number of non-NULL observations.
Your First AVG Query
Consider a small 3-person team with salaries: [40000, 50000, 60000].
Manual Verification: (40,000 + 50,000 + 60,000) ÷ 3 = 150,000 ÷ 3 = 50,000.
Sum = 180,000
AVG With WHERE
When a WHERE filter is present, the database filters candidate rows before computing the mean:
FROM employees
WHERE department = 'Sales';
AVG and NULL (Exclusion Rules)
This is one of the most critical concepts in SQL data analytics: AVG() completely ignores NULL input values.
Given 4 records: [40, NULL, 60, 80]:
- The numerator (Sum) adds only non-NULL numbers:
40 + 60 + 80 = 180. - The denominator (Count) counts only non-NULL entries:
3 items. - The calculation is:
180 ÷ 3 = 60(NOT 180 ÷ 4).
Discard NULL ➔ [40, 60, 80]
AVG vs Zero (Crucial Difference)
NULL means missing or unknown, whereas 0 is a known numeric measurement:
(40 + 60 + 80) ÷ 3 = 180 ÷ 3 = 60
(40 + 0 + 60 + 80) ÷ 4 = 180 ÷ 4 = 45
Count = 3 ➔ AVG = 60
Count = 4 ➔ AVG = 45
AVG With No Non-NULL Values
What happens when AVG() is run on an empty set or on rows where every value is NULL?
AVG() returns NULL when there are zero non-NULL rows. It does not return 0.AVG With Calculated Expressions
You can aggregate arithmetic expressions directly:
FROM order_items;
AVG(DISTINCT ...) Patterns
Adding DISTINCT inside AVG() eliminates duplicate values before calculating the arithmetic mean:
FROM reviews;
Given ratings [5, 5, 5, 1]: standard AVG is (16 ÷ 4) = 4.0. However, AVG(DISTINCT) averages unique ratings [5, 1] giving (6 ÷ 2) = 3.0.
AVG vs SUM and COUNT
The mathematical triad of SQL aggregates:
| Function | Formula / Action | Example with [40, NULL, 60] |
|---|---|---|
SUM(col) | Adds numeric values | 40 + 60 = 100 |
COUNT(col) | Counts non-NULL observations | 2 observations |
AVG(col) | SUM(col) ÷ COUNT(col) | 100 ÷ 2 = 50 |
Understanding AVG Results
The calculated mean does not have to be one of the values present in the table. For example, the average of [10, 20] is 15 (even though neither row has the value 15).
| # | Name | Dept | Salary | AVG Inclusion Status |
|---|---|---|---|---|
| 1 | Aarav Patel | Engineering | ₹75,000 | ✓ ₹75,000 added to sum & count |
| 2 | Diya Sharma | Sales | NULL | ✗ Salary is NULL (Ignored in sum & count) |
| 3 | Ishaan Verma | Engineering | ₹60,000 | ✓ ₹60,000 added to sum & count |
| 4 | Riya Gupta | Sales | ₹45,000 | ✓ ₹45,000 added to sum & count |
| 5 | Kabir Singh | Marketing | ₹50,000 | ✓ ₹50,000 added to sum & count |
| 6 | Ananya Roy | Sales | ₹45,000 | ✓ ₹45,000 added to sum & count |
| 7 | Rohan Mehta | Marketing | NULL | ✗ Salary is NULL (Ignored in sum & count) |
| 8 | Tara Nair | Engineering | ₹90,000 | ✓ ₹90,000 added to sum & count |
SELECT AVG(salary) AS avg_company_salary FROM employees;
avg_company_salaryCommon AVG Mistakes
Remember that NULL values do not reduce the average; they are completely omitted from both the numerator and denominator.
If 0 rows match your WHERE filter, AVG() returns NULL, not 0.
SUM gives the grand total, COUNT gives the number of items, and AVG divides SUM by COUNT.
Practical Step-by-Step Exercises
| Task Goal | Target Table | Required SQL Solution | Pattern Used |
|---|---|---|---|
| 1. Average Employee Salary | employees | SELECT AVG(salary) FROM employees; | Basic column AVG |
| 2. Average Product Price | products | SELECT AVG(price) FROM products; | Catalog pricing mean |
| 3. Average Sales for Electronics | sales | SELECT AVG(amount) FROM sales WHERE category = 'Electronics'; | WHERE + AVG |
| 4. Average Line-Item Value | order_items | SELECT AVG(price * quantity) FROM order_items; | Expression average |
| 5. Average Distinct Rating | reviews | SELECT AVG(DISTINCT rating) FROM reviews; | Deduplicated distinct mean |
AVG Best Practices
- Always Alias Output: Write
AVG(salary) AS average_salaryfor clarity. - Be Aware of NULL Impact: Recognize that omitting missing values changes both the total sum and the dividing count.
- Use WHERE for Specific Cohorts: Filter by date, status, or category prior to aggregation.
- Format Decimals in Presentation Layer: Databases return floating precision; format decimals cleanly in UI components.
What You Should Know Now
- ✓AVG(col): Calculates arithmetic mean
- ✓AVG(expr): Averages computed expressions (e.g. price * qty)
- ✓NULL Exclusion: NULL rows are ignored in sum and count
- ✓NULL vs Zero: 0 decreases the mean; NULL is skipped
- ✓Empty Sets: Returns NULL when 0 valid rows match
- ✓AVG(DISTINCT): Averages unique values only
🎯 Knowledge Check Quiz: SQL AVG
Test your understanding of arithmetic means, NULL handling, zero vs NULL impact, and distinct averages.