AVG function in SQL - Deep Dive

Overview - AVG function

What is it?

The AVG function in SQL calculates the average value of a numeric column in a table. It adds up all the numbers in that column and then divides by the count of those numbers. This helps find the typical or central value in a set of data. It works only with numbers and ignores empty or missing values.

Why it matters

Without the AVG function, finding the average of many numbers stored in a database would require manual calculation or complex code. This function saves time and reduces errors, making it easy to summarize data like average sales, temperatures, or test scores. It helps businesses and people make decisions based on typical values rather than individual data points.

Where it fits

Before learning AVG, you should understand basic SQL SELECT queries and how to work with tables and columns. After mastering AVG, you can learn other aggregate functions like SUM, COUNT, MIN, and MAX, and how to group data using GROUP BY for more detailed summaries.

Mental Model

Core Idea

AVG finds the middle value by adding all numbers and dividing by how many there are.

Think of it like...

Imagine you have a basket of apples with different weights. To find the average weight, you weigh all apples together and then divide by the number of apples. AVG does the same with numbers in a database column.

┌───────────────┐
│ Numeric Data  │
│ 10, 20, 30, 40│
└──────┬────────┘
       │
       ▼
┌─────────────────────────┐
│ AVG = (10+20+30+40) / 4 │
│        = 100 / 4 = 25   │
└─────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What AVG Function Does

Concept: AVG calculates the average of numbers in a column.

AVG adds all the numbers in a column and divides by the count of those numbers. It ignores NULL or missing values automatically. For example, if a column has 10, 20, 30, AVG returns (10+20+30)/3 = 20.

Result

You get a single number representing the average of the column's values.

Understanding AVG as a simple sum divided by count helps you grasp how it summarizes data quickly.

2

Foundation

Using AVG in a Basic Query

Concept: How to write a SQL query using AVG.

To find the average price from a products table, you write: SELECT AVG(price) FROM products; This returns one number: the average price of all products.

Result

The query returns the average value of the price column.

Knowing the syntax lets you quickly get average values without extra calculations.

3

Intermediate

AVG Ignores NULL Values

🤔Before reading on: Do you think AVG counts NULL values as zero or ignores them? Commit to your answer.

Concept: AVG does not count NULLs when calculating the average.

If a column has values 10, NULL, 20, AVG adds only 10 and 20, then divides by 2, not 3. So AVG returns (10+20)/2 = 15, ignoring the NULL.

Result

The average excludes missing data, giving a true average of existing numbers.

Knowing NULLs are ignored prevents mistakes when interpreting averages with missing data.

4

Intermediate

Combining AVG with GROUP BY

🤔Before reading on: Will AVG calculate one average for the whole table or separate averages per group? Commit to your answer.

Concept: AVG can calculate averages for groups of rows using GROUP BY.

If you want average sales per region: SELECT region, AVG(sales) FROM sales_data GROUP BY region; This returns average sales for each region separately.

Result

You get multiple averages, one per group defined by GROUP BY.

Grouping data before averaging lets you compare averages across categories easily.

5

Intermediate

AVG with Filtering Using WHERE

Concept: You can filter rows before averaging using WHERE clause.

To find average sales only for 2023: SELECT AVG(sales) FROM sales_data WHERE year = 2023; This averages sales from rows where year is 2023 only.

Result

The average reflects only the filtered subset of data.

Filtering before averaging lets you focus on specific data slices for more relevant insights.

6

Advanced

AVG on Expressions and Calculated Columns

🤔Before reading on: Can AVG work on calculations like price * quantity or only on raw columns? Commit to your answer.

Concept: AVG can calculate averages on expressions, not just columns.

You can average results of calculations: SELECT AVG(price * quantity) FROM orders; This averages the total price per order line, not just price or quantity alone.

Result

You get the average of the calculated values, enabling flexible summaries.

Using expressions inside AVG expands its power to summarize complex data.

7

Expert

AVG and Data Types Impact on Results

🤔Before reading on: Does AVG always return the same data type as the input column? Commit to your answer.

Concept: AVG's return type depends on input data types and can affect precision.

If AVG is used on integers, some SQL systems return a decimal or float to avoid losing fractions. But in some cases, it may truncate decimals if not careful. Understanding how your database handles this prevents rounding errors.

Result

You get accurate averages with correct data types, avoiding surprises in results.

Knowing AVG's data type behavior helps ensure precise calculations and prevents subtle bugs.

Under the Hood

AVG works by scanning the selected rows, summing all non-NULL numeric values, and counting how many values were summed. Then it divides the sum by the count. Internally, the database engine optimizes this by streaming data and using efficient aggregation algorithms, sometimes using indexes or parallel processing for large datasets.

Why designed this way?

AVG was designed as a simple aggregate function to provide a quick summary of numeric data. Ignoring NULLs avoids skewing results with missing data. The division approach is intuitive and mathematically sound. Alternatives like median or mode are more complex and less efficient, so AVG is a fast, general-purpose summary.

┌───────────────┐
│ Input Rows    │
│ (with numbers)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Sum Non-NULL   │
│ Values        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Count Non-NULL │
│ Values        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Divide Sum by │
│ Count         │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return AVG    │
│ Value         │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does AVG count NULL values as zero or ignore them? Commit to your answer.

Common Belief: AVG treats NULL values as zero when calculating the average.

Reality: AVG completely ignores NULL values and does not count them in the total or divisor.

Why it matters: If you think NULLs are zeros, you might underestimate the average, leading to wrong conclusions about data quality or performance.

Quick: Does AVG always return an integer if input is integer? Commit to your answer.

Common Belief: AVG returns the same data type as the input column, so integer input means integer output.

Reality: Most SQL systems return a decimal or float for AVG, even if input is integer, to preserve fractional averages.

Why it matters: Assuming integer output can cause bugs when decimals are truncated or rounded unexpectedly.

Quick: Does AVG calculate the average of all rows regardless of grouping? Commit to your answer.

Common Belief: AVG always calculates one average for the entire table, ignoring groups.

Reality: When used with GROUP BY, AVG calculates separate averages per group.

Why it matters: Misunderstanding grouping leads to wrong data summaries and misinterpretation of grouped reports.

Quick: Can AVG be used on non-numeric columns like text? Commit to your answer.

Common Belief: AVG can be used on any column, including text or dates.

Reality: AVG only works on numeric data types; using it on non-numeric columns causes errors.

Why it matters: Trying to average non-numeric data wastes time and causes query failures.

Expert Zone

1

AVG's behavior with floating-point numbers can introduce tiny rounding errors due to how computers store decimals.

2

Some databases optimize AVG by combining SUM and COUNT operations in a single pass for performance on large datasets.

3

When AVG is used with DISTINCT, it averages only unique values, which can drastically change results.

When NOT to use

AVG is not suitable when you need the middle value (median) or the most frequent value (mode). For skewed data, median is better. Also, avoid AVG on categorical or non-numeric data; use COUNT or other functions instead.

Production Patterns

In real systems, AVG is often combined with GROUP BY and HAVING clauses to filter groups by average thresholds. It's also used in dashboards to show KPIs like average revenue or average response time. Indexes on numeric columns can speed up AVG calculations on large tables.

Connections

Median

Related aggregate function that finds the middle value instead of the mean.

Knowing AVG helps understand median as another way to summarize data, especially when data is skewed.

MapReduce (Big Data)

AVG is implemented as a reduce operation that combines sums and counts from map tasks.

Understanding AVG's sum and count aggregation helps grasp how distributed systems compute averages efficiently.

Arithmetic Mean in Statistics

AVG is the database equivalent of the arithmetic mean concept in statistics.

Knowing AVG connects database queries to fundamental statistical measures used in data analysis.

Common Pitfalls

#1 Including NULL values in the average calculation incorrectly.

Wrong approach:

SELECT SUM(price) / COUNT(*) FROM products;

Correct approach:

SELECT AVG(price) FROM products;

Root cause:

Using COUNT(*) counts all rows including those with NULLs, skewing the average calculation.

#2 Using AVG on non-numeric columns causing errors.

Wrong approach:

SELECT AVG(product_name) FROM products;

Correct approach:

SELECT AVG(price) FROM products;

Root cause:

AVG requires numeric data; applying it to text columns is invalid.

#3 Assuming AVG returns integer when input is integer, losing decimals.

Wrong approach:

SELECT CAST(AVG(quantity) AS INT) FROM orders;

Correct approach:

SELECT AVG(quantity) FROM orders;

Root cause:

Casting AVG result to integer truncates decimals, losing precision.

Key Takeaways

The AVG function calculates the average by summing numeric values and dividing by their count, ignoring NULLs.

AVG works only on numeric columns and returns a decimal or float to preserve precision.

Using AVG with GROUP BY lets you find averages per category or group, enabling detailed data summaries.

Filtering data with WHERE before AVG focuses the average on relevant subsets.

Understanding AVG's behavior with NULLs and data types prevents common mistakes and ensures accurate results.

Practice

1. What does the SQL AVG() function do?

easy

A. Calculates the average value of a numeric column

B. Counts the number of rows in a table

C. Finds the maximum value in a column

D. Returns the sum of all values in a column

Solution

1. Step 1: Understand the purpose of AVG()

   The AVG() function is designed to calculate the average (mean) of numeric values in a column.

2. Step 2: Compare with other aggregate functions

   Unlike COUNT(), MAX(), or SUM(), AVG() specifically returns the average value.

3. Final Answer:

   Calculates the average value of a numeric column -> Option A

4. Quick Check:

   AVG() = average calculation [OK]

Hint: AVG() always returns the mean of numbers, not counts or sums [OK]

Common Mistakes:

• Confusing AVG() with COUNT()
• Thinking AVG() sums values without dividing
• Assuming AVG() works on non-numeric columns

2. Which of the following is the correct syntax to find the average salary from a table named Employees?

easy

A. SELECT AVG salary FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT AVERAGE(salary) FROM Employees;

D. SELECT salary AVG() FROM Employees;

Solution

1. Step 1: Recall correct AVG() syntax

   The AVG() function requires parentheses around the column name: AVG(column_name).

2. Step 2: Check each option

   Correct syntax uses AVG(salary) with parentheses. Missing parentheses, AVERAGE(salary), or salary AVG() cause syntax errors.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: AVG() always needs parentheses around the column name [OK]

Common Mistakes:

• Omitting parentheses in AVG()
• Using wrong function name like AVERAGE()
• Placing AVG() after column name incorrectly

3. Given the table Scores with values:
Score
90
80
NULL
70
What will the query SELECT AVG(Score) FROM Scores; return?

medium

A. 240

B. NULL

C. 80

D. 75

Solution

1. Step 1: Identify values considered by AVG()

   AVG() ignores NULL values, so it averages 90, 80, and 70.

2. Step 2: Calculate the average

   (90 + 80 + 70) / 3 = 240 / 3 = 80.

3. Final Answer:

   80 -> Option C

4. Quick Check:

   AVG ignores NULL, average = 80 [OK]

Hint: AVG() skips NULLs automatically when averaging [OK]

Common Mistakes:

• Including NULL as zero in average
• Returning NULL if any NULL exists
• Summing values without dividing

4. Consider this query:
SELECT AVG(price) FROM Products WHERE price > 0;
It returns NULL even though there are products with price 0 and above. What is the likely problem?

medium

A. The WHERE clause excludes all rows because price > 0 filters out zero prices

B. AVG() cannot be used with WHERE clause

C. The price column contains only NULL values

D. AVG() requires GROUP BY to work

Solution

1. Step 1: Analyze the WHERE clause condition

   The condition price > 0 excludes prices equal to zero, so only prices greater than zero are included.

2. Step 2: Consider data and NULL result

   If no prices are greater than zero, the filtered set is empty, so AVG() returns NULL.

3. Final Answer:

   The WHERE clause excludes all rows because price > 0 filters out zero prices -> Option A

4. Quick Check:

   Empty filtered rows cause AVG() to return NULL [OK]

Hint: Check WHERE filters exclude all rows causing NULL AVG() [OK]

Common Mistakes:

• Thinking AVG() can't use WHERE
• Assuming AVG() needs GROUP BY always
• Ignoring that empty sets return NULL

5. You have a table Sales with columns Region and Amount. How do you write a query to find the average sales amount per region, excluding regions with no sales?

hard

A. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount IS NOT NULL;

B. SELECT Region, AVG(Amount) FROM Sales WHERE Amount > 0;

C. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount > 0;

D. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL;

Solution

1. Step 1: Group sales by region

   Use GROUP BY Region to calculate average per region.

2. Step 2: Exclude regions with no sales

   Regions with no sales have AVG(Amount) as NULL, so use HAVING AVG(Amount) IS NOT NULL to filter them out.

3. Final Answer:

   SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL; -> Option D

4. Quick Check:

   GROUP BY + HAVING filters NULL averages [OK]

Hint: Use HAVING to exclude groups with NULL averages [OK]

Common Mistakes:

• Using WHERE after GROUP BY (invalid syntax)
• Not filtering NULL averages with HAVING
• Filtering rows before grouping instead of after